locked
Most Recent Record by Distinct 2nd Column RRS feed

  • Question

  • User1122680954 posted

    I've got an ASP.NET application that is supposed to pull the most recent record (DateReady) of each distinct instance of another column (Dock), based on whether the records meet the criteria of a third column (Building). This is for an SqlDataSource object.

    For example, here is a starting data set.

    ID    Dock   Building   DateReady

    1       1         CONV    01/31/2014 5:16

    2       1         CONV    01/31/2014 6:26

    3       2         CONV    01/31/2014 4:35

    4       2         CONV    01/31/2014 7:32

    5       1         FILM1    01/31/2014 8:30

    Here is the result I want:

    2       1         CONV    01/31/2014 6:26

    4       2         CONV    01/31/2014 7:32

    In this case, I want the most recent Record for each Dock in the Building which matches 'CONV'.  I've been working on this all day, and have found many different examples which I've followed.  Most of these just gave me errors.  The code below doesn't give me any errors, but doesn't give me any results either.  I'm missing something obvious, but I'm just not finding it.

    SELECT tt.* 
        FROM [TransferTrucks] AS tt
        INNER JOIN
            (SELECT [Dock], MAX([DateReady]) AS MaxTime 
            FROM [TransferTrucks] GROUP BY [Dock]) AS Groupedtt
            ON tt.[Dock] = Groupedtt.[Dock] AND tt.[DateReady] = Groupedtt.MaxTime
            WHERE [Building] = 'CONV'

    Friday, January 31, 2014 3:34 PM

Answers

  • User77042963 posted
    SELECT ID,Dock,Building,DateReady FROM (SELECT *,
    Row_number() Over(Partition By Dock  Order By DateReady DESC ) rn
    FROM [TransferTrucks] WHERE  Building = 'CONV') t
    WHERE rn=1
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 1, 2014 12:33 PM

All replies

  • User2117486576 posted

    Try this (caveat: I did this without intellisense):

    select tt.*
    from TransferTrucks tt
    where tt.Building = 'CONV'
    and tt.DateReady = (select max(x.DateReady) from TransferTrucks x where x.Dock = tt.Dock)

    Friday, January 31, 2014 6:21 PM
  • User77042963 posted
    SELECT ID,Dock,Building,DateReady FROM (SELECT *,
    Row_number() Over(Partition By Dock  Order By DateReady DESC ) rn
    FROM [TransferTrucks] WHERE  Building = 'CONV') t
    WHERE rn=1
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 1, 2014 12:33 PM
  • User1122680954 posted

    Thanks, limno.  That worked perfectly.  Thanks for your help.

    Monday, February 3, 2014 11:07 AM
  • User1122680954 posted

    Thanks for the reply, Richard.  I tried your code but it didn't return any data.  But, then I tried limno's code and it worked perfectly. Thanks for your help in any case.

    Monday, February 3, 2014 11:09 AM