Getting the most recent record

Question

Post reply

Getting the most recent record

barry555

SSC Eights!

Points: 944
More actions
November 12, 2006 at 9:01 am

#175357
I have a table with data that looks like this:
PupilID CourseID StartDate
1 11 12/03/2006
1 12 03/01/2004
1 13 12/12/2003
2 17 01/11/2004
2 14 23/12/2005
For each pupil, I want to get the PupilID and CourseID of the most recently started course:
PupilID CourseID
1 11
2 14
I have the following query (which I think is OK!):
```
SELECT t.PupilID, t.CourseIDFROM Test t INNER JOIN (SELECT PupilID, Max(StartDate) As StartDateFROM TestGROUP BY PupilID) de ON t.PupilID = de.PupilIDAnd t.StartDate = de.StartDate
ORDER BY t.PupilID 
```
However, I wondered if there was a way of doing this in one query rather than using a derived table. This query will be used a lot against a large table so I want to optimise the T-SQL.
Thanks,
Barry

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

No that is exactly how to do it.

It is actually very fast, and will not cause much additional overhead.

Sergiy SSC Guru Points: 110192 More actions · Answer 2

Just make sure indexing is OK.

Clustered index on (PupilID, StartDate) + index on StartDate will make this query terribly fast.

_____________
Code for TallyGenerator

LJvanderEnt SSC Enthusiast Points: 156 More actions · Answer 3

You could also use this one:

SELECT t.PupilID,

(SELECT Top 1 CourseID FROM Test WHERE PupilID = t.PupilID ORDER BY StartDate)

FROM Test t

LJvanderEnt SSC Enthusiast Points: 156 More actions · Answer 4

OK, it is Monday morning, so I forgot something to declare..

SELECT t.PupilID,

(SELECT Top 1 CourseID FROM Test WHERE PupilID = t.PupilID ORDER BY StartDate) AS CourseID

FROM Test t

Sergiy SSC Guru Points: 110192 More actions · Answer 5

Don't ever use anything like this.

They name it "hidden cursor", and performance of such queries is terrible.

_____________
Code for TallyGenerator

LJvanderEnt SSC Enthusiast Points: 156 More actions · Answer 6

Hmm, got your point.

I tested against a database with 339642 records and got 18302 records back in 1 second using the proposed query against 5 seconds with my own proposed query. Learned something today and have to rewrite some queries....

pasha701 Grasshopper Points: 16 More actions · Answer 7

SELECT t.PupilID,

(SELECT Top 1 CourseID FROM Test WHERE PupilID = t.PupilID ORDER BY StartDate Desc) AS CourseID

FROM Test t

Group by t.PupilID

-- here must add

-- Desc and Group by