Earliest record - query help please.

Hi all,

I'm struggling to get my head around the syntax to achieve my results. The scenario is this, users can take assessments as many times as they wish, but only the first assessment score is of significance. The data is held in three tables, AssessmentResult (for the assessment score, date taken, course ID and the user ID), UserData (for the users name) and the Courses table (which holds the course names).

In joining them the AssessmentResults table would join to the UserData table by the UserID and the Courses table would join to the AssessmentResults table by the CourseID, this then produces a list of results:

16      49      12      8      93      3      2004-11-08 16:59:24.560      David J Smith      Basic Electrics
17      12      39      3      80      2      2004-11-15 12:03:19.247      Tim Simcock      Inspirational Leadership of Service and Parts Departments
18      12      39      3      84      2      2004-11-15 14:15:51.873      Tim Simcock      Inspirational Leadership of Service and Parts Departments

As you can see a chap called Tim Timcock has taken an assessment on course 12 twice, the only result we're concerned with is the earliest one (taken first) which is 2004-11-15 12:03:19.247.

Attached is the SQL which produced the results above, can someone expand it so we only get a single, earliest, value for each assessment?

Thanks.Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

select ar.RecordID, 
	ar.UserID,
	ar.CourseID,
	ar.AssessmentID,
	ar.Result,
	ar.Pass,
	ar.Date_Created,
	ud.Fname + ' ' + ud.Sname as fullName,
	co.CourseName
	from dbo.AssessmentResult ar
	inner join dbo.UserData ud on ar.UserID = ud.UserID
	inner join dbo.Course co on ar.CourseID = co.CourseID
	order by ar.Date_Created ASC