Post reply

Getting the most recent record

  • 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.CourseID
    FROM
     Test t INNER JOIN (SELECT PupilID, Max(StartDate) As StartDateFROM TestGROUP BY PupilID) de ON t.PupilID = de.PupilID
    And 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

  • November 12, 2006 at 10:03 am

    #671247

    No that is exactly how to do it.

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

     

  • November 12, 2006 at 12:26 pm

    #671249

    Just make sure indexing is OK.

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

    _____________
    Code for TallyGenerator

  • November 13, 2006 at 2:12 am

    #671274

    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

  • November 13, 2006 at 2:16 am

    #671276

    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

  • November 13, 2006 at 2:49 am

    #671279

    Don't ever use anything like this.

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

    _____________
    Code for TallyGenerator

  • November 13, 2006 at 3:20 am

    #671280

    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....

  • November 13, 2006 at 5:22 am

    #671296

    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

     

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

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