Most Recent Record by Distinct 2nd Column

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'

Answer 1

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)

Answer 2

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
 

Answer 3

User1122680954 posted

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

Answer 4

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.