none
Selecting 2nd through n most recent records for each key field RRS feed

  • Question

  • This is my existing query, which provides the comments that are not the most current status for each txtISSUE_TRACK_REF

    SELECT  POST.txtISSUE_TRACK_REF, POST.dtmEDIT_DATE, POST.memPOSTED_COMMENT
    FROM (SELECT txtISSUE_TRACK_REF, MAX(dtmEDIT_DATE) AS dtmLATEST_DATE FROM [dbo_~tblPostedComments] GROUP BY txtISSUE_TRACK_REF)  AS LATEST INNER JOIN [dbo_~tblPOSTEDCOMMENTS] AS POST ON (LATEST.dtmLATEST_DATE <> POST.dtmEDIT_DATE) AND (LATEST.txtISSUE_TRACK_REF = POST.txtISSUE_TRACK_REF)
    ORDER BY POST.txtISSUE_TRACK_REF, POST.dtmEDIT_DATE DESC

    I would like to limit this to the top n for each key field, 5 or 3 or whatever the user chooses. If I change the beginning to
    Select TOP 5 POST.txtISSUE_TRACK_REF, I get only 5 records, as expected, instead of five for each key field.  Any ideas on what can be done?

    Thanks

     

    Thursday, January 5, 2012 9:33 PM

Answers

  • Hi AnotherBiggles,

    You might try to use the TOP in the Subquery, so the SQL string will be like this:

    SELECT  POST.txtISSUE_TRACK_REF, POST.dtmEDIT_DATE, POST.memPOSTED_COMMENT
    FROM (SELECT TOP 5 txtISSUE_TRACK_REF, MAX(dtmEDIT_DATE) AS dtmLATEST_DATE FROM [dbo_~tblPostedComments] GROUP BY txtISSUE_TRACK_REF)  AS LATEST INNER JOIN [dbo_~tblPOSTEDCOMMENTS] AS POST ON (LATEST.dtmLATEST_DATE <> POST.dtmEDIT_DATE) AND (LATEST.txtISSUE_TRACK_REF = POST.txtISSUE_TRACK_REF)
    ORDER BY POST.txtISSUE_TRACK_REF, POST.dtmEDIT_DATE DESC
    

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, January 19, 2012 6:29 PM
    Moderator