Pulling unique records with max dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pulling unique records with max dates

Author

Topic

Nixter
Starting Member

5 Posts

Posted - 2006-03-05 : 18:14:32

I am trying to pull only those records with a maximum upload date for a file upload log.

This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.

The field names are:
SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.

So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...

Thanks Much in advance

indieman
Starting Member

12 Posts

Posted - 2006-03-05 : 20:26:44

edit: someone beat me to it....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-05 : 20:28:02

try this :

select SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
from   yourtable t
inner join
(
    select SupplierID, max(UploadDate) as max_UploadDate
    from   yourtable
    group by SupplierID
) as m
on  t.SupplierID = m.SupplierID
and t.UploadDate = m.max_UploadDate

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-05 : 20:35:20

quote:
Originally posted by indieman

edit: someone beat me to it....

It happends all the time. Don't worry about this. You might have highlighted some points that I might not have spotted.

----------------------------------
'KH'

Nixter
Starting Member

5 Posts

Posted - 2006-03-05 : 20:38:04

Thanks folks! Not totally new to SQL but this one had me totally racked. I suppose working on a Sunday will do that to you! Thanks again - this forum rocks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources