Top 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Top 1

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2012-08-09 : 07:34:09

Hi
I'm trying to make my query return only the first instance of when they received a specific mark against them.

For example, I have a list of people with the specific mark and the dates and every record shows as follows

Ref No Name Mark Date
111111 Bob W 20/02/2012
111111 Bob W 05/04/2012
111111 Bob W 01/05/2012
111111 Bob W 20/06/2012
111111 Bob W 28/06/2012

I've tried TOP 1 but it just returns the first person.

This is my current SQL

SELECT     TOP (100) PERCENT RegisterSession.Date, StudentDetail.AcademicYearID, StudentDetail.RefNo, 
                      StudentDetail.FirstForename, StudentDetail.Surname, Offering.OfferingID, 
                      Offering.Code, Offering.Name, Register.RegisterID, Register.RegisterNo, 
                      Register.Title, MarkType.Mark, MarkType.Description, 
                      Enrolment.CompletionStatusID, Enrolment.StartDate, Enrolment.ExpectedEndDate, 
                      Enrolment.ActualEndDate, RegisterSession.Date AS Expr1
FROM         StudentDetail WITH (NOLOCK) INNER JOIN
                      Enrolment WITH (NOLOCK) ON Enrolment.StudentDetailID = StudentDetail.StudentDetailID INNER JOIN
                      Offering WITH (NOLOCK) ON Enrolment.OfferingID = Offering.OfferingID INNER JOIN
                      RegisterStudent WITH (NOLOCK) ON 
                      Enrolment.EnrolmentID = RegisterStudent.EnrolmentID INNER JOIN
                      Register WITH (NOLOCK) ON RegisterStudent.RegisterID = Register.RegisterID INNER JOIN
                      RegisterSession WITH (NOLOCK) ON Register.RegisterID = RegisterSession.RegisterID INNER JOIN
                      RegisterMark WITH (NOLOCK) ON 
                      RegisterSession.RegisterSessionID = RegisterMark.RegisterSessionID AND 
                      RegisterStudent.RegisterStudentID = RegisterMark.RegisterStudentID INNER JOIN
                      MarkType WITH (NOLOCK) ON RegisterMark.MarkTypeID = MarkType.MarkTypeID
GROUP BY StudentDetail.RefNo, StudentDetail.AcademicYearID, StudentDetail.FirstForename, 
                      StudentDetail.Surname, Offering.OfferingID, Offering.Code, Offering.Name, 
                      Register.RegisterID, Register.RegisterNo, Register.Title, 
                      Enrolment.CompletionStatusID, Enrolment.StartDate, Enrolment.ExpectedEndDate, 
                      Enrolment.ActualEndDate, MarkType.Mark, MarkType.Description, 
                      RegisterSession.Date
HAVING      (StudentDetail.AcademicYearID = '11/12') AND (Enrolment.CompletionStatusID = '1') AND 
                      (MarkType.Mark = 'W')
ORDER BY StudentDetail.RefNo, RegisterSession.Date

Can anyone point me in the right direction please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-09 : 07:54:59

[code];WITH cteSource([Date], AcademicYearID, RefNo, FirstForename, Surname, OfferingID, Code, Name, RegisterID, RegisterNo, Title, Mark, [Description], CompletionStatusID, StartDate, ExpectedEndDate, ActualEndDate, Expr1, rn)
AS (
SELECT ses.[Date],
sd.AcademicYearID,
sd.RefNo,
sd.FirstForename,
sd.Surname,
o.OfferingID,
o.Code,
o.Name,
r.RegisterID,
r.RegisterNo,
r.Title,
mt.Mark,
mt.[Description],
e.CompletionStatusID,
e.StartDate,
e.ExpectedEndDate,
e.ActualEndDate,
ses.[Date] AS Expr1,
ROW_NUMBER() OVER (PARTITION BY sd.RefNo ORDER BY ses.[Date]) AS rn
FROM dbo.StudentDetail WITH (NOLOCK) AS sd
INNER JOIN dbo.Enrolment WITH (NOLOCK) AS e ON e.StudentDetailID = sd.StudentDetailID
AND e.CompletionStatusID = '1'
INNER JOIN dbo.Offering WITH (NOLOCK) AS o ON o.OfferingID = e.OfferingID
INNER JOIN dbo.RegisterStudent WITH (NOLOCK) AS stu ON stu.EnrolmentID = e.EnrolmentID
INNER JOIN dbo.Register WITH (NOLOCK) AS r ON r.RegisterID = stu.RegisterID
INNER JOIN dbo.RegisterSession WITH (NOLOCK) AS ses ON ses.RegisterID = r.RegisterID
INNER JOIN dbo.RegisterMark WITH (NOLOCK) AS rm ON rm.RegisterSessionID = ses.RegisterSessionID
INNER JOIN dbo.MarkType WITH (NOLOCK) AS mt ON MarkType.MarkTypeID = rm.MarkTypeID
AND mt.Mark = 'W'
WHERE sd.AcademicYearID = '11/12'
AND rm.RegisterStudentID = rs.RegisterStudentID
)
SELECT [Date],
AcademicYearID,
RefNo,
FirstForename,
Surname,
OfferingID,
Code,
Name,
RegisterID,
RegisterNo,
Title,
Mark,
[Description],
CompletionStatusID,
StartDate,
ExpectedEndDate,
ActualEndDate,
Expr1
FROM cteSource
WHERE rn = 1
ORDER BY RefNo;[/code]

N 56°04'39.26"
E 12°55'05.63"

chipembele
Posting Yak Master

106 Posts

Posted - 2012-08-09 : 08:56:42

THanks. worked it out. Works fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources