[RESOLVED] SQL Server 2000 Query Help returning first attempted record

[fullyii]

SQL Server 2000.

I am trying to write a query that obtains the first record submitted by a user. The user can take a test as many times as they want I just need to know how to obtain the first attempt.

Here is my first attempt and it returns multiple attemps though. A nudge in the correct direction would be greatly appreciated



SELECT Distinct tbl_security.UserId,
tbl_security.Username,
tbl_coursecodes.coursecode,
tbl_quizresults.surveycode,
tbl_quizresults.score,
MIN(tbl_quizresults.startdate)




FROM

CPTD.dbo.tbl_coursecodes tbl_coursecodes
join CPTD.dbo.tbl_surveycodes tbl_surveycodes
on tbl_coursecodes.coursecode = tbl_surveycodes.coursecode
join CPTD.dbo.tbl_surveyquizzes tbl_surveyquizzes
on tbl_surveycodes.rowid = tbl_surveyquizzes.surveycode
join CPTD.dbo.tbl_quizzes tbl_quizzes
on tbl_surveyquizzes.quizid = tbl_quizzes.rowid
join CPTD.dbo.tbl_quizversions tbl_quizversions
on tbl_quizzes.rowid = tbl_quizversions.quizid
join CPTD.dbo.tbl_quizresults tbl_quizresults
on tbl_quizversions.rowid = tbl_quizresults.quizid
join CPTD.dbo.tbl_security tbl_security
on tbl_security.rowid = tbl_quizresults.userid


Where tbl_quizresults.startdate >= '2006-09-01'
AND tbl_coursecodes.coursecode = 'CSS100012'


Group By tbl_security.UserId,
tbl_security.Username,
tbl_coursecodes.coursecode,
tbl_quizresults.surveycode,
tbl_quizresults.score

[Al42]

The 'first' record in the database may not be the first one saved. Either save the date/time and retrieve the earliest date/time record for that user, or number the records (an autonumber field will do) and retrieve the lowest number record for that user.

[fullyii]

The datetime is saved as startdate. I am not sure how to write the query to return

tbl_security.UserId,
tbl_quizresults.score As First_Score,

[si_the_geek]

The way to do it is to use a sub-query to limit the results for tbl_quizresults (as it is where Score comes from) the to only the lowest date for that user.

Luckily you have the userid and startdate in that table too, so it will be a short sub-query. Simply add this to your Where clause:

AND tbl_quizresults.startdate = (
    SELECT Min(qr2.startdate)
    FROM CPTD.dbo.tbl_quizresults qr2
    WHERE qr2.userid = tbl_quizresults.userid
    )

[fullyii]

Thanks That was exactly what I needed!

[si_the_geek]

Good stuff, glad to have helped.

As you have the answer, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".