Last post Feb 03, 2014 11:09 AM by lewis886
Jan 31, 2014 03:34 PM|lewis886|LINK
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:
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.
FROM [TransferTrucks] AS tt
(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'
Jan 31, 2014 06:21 PM|RichardY|LINK
Try this (caveat: I did this without intellisense):
from TransferTrucks tt
where tt.Building = 'CONV'
and tt.DateReady = (select max(x.DateReady) from TransferTrucks x where x.Dock = tt.Dock)
Feb 01, 2014 12:33 PM|limno|LINK
SELECT ID,Dock,Building,DateReady FROM (SELECT *,
Row_number() Over(Partition By Dock Order By DateReady DESC ) rn
FROM [TransferTrucks] WHERE Building = 'CONV') t
Feb 03, 2014 11:07 AM|lewis886|LINK
Thanks, limno. That worked perfectly. Thanks for your help.
Feb 03, 2014 11:09 AM|lewis886|LINK
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.