Select most recent record by Product + Supplier

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

Select most recent record by Product + Supplier

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-27 : 23:56:18

I am trying to pickup the most recent record by Product and Supplier.
eg.

Rec Prod Supp Date
1 1000 WES01 Jan 1/2005
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
The Result I want is as follows:

Rec Prod Supp Date
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
I have tried various methods with the TOP 1 and DISTINCT statements, but have not been able to get the results I am looking for. Help would be appreciated.

Kristen
Test

22859 Posts

Posted - 2005-08-28 : 03:05:41

SELECT Prod, Supp, MAX(Date)
FROM MyTable
GROUP BY Prod, Supp

Hopefully you don't want the Rec number too - if you do that's a bit more tricky, in particular how to handle situation where you have more than one Rec value for a given Prod / Supp

Kristen

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-28 : 03:11:10

I need the Record # as well as some other pricing fields. I'm trying to select the most recent pricing info for a Product and Supplier.

Kristen
Test

22859 Posts

Posted - 2005-08-28 : 03:16:04

I'll leave you to deal with any duplicates then


SELECT M.*
FROM MyTable AS M
     JOIN
     (
         SELECT Prod, Supp, MAX(Date) AS MaxDate
         FROM MyTable
         GROUP BY Prod, Supp
     ) AS T
         ON T.Prod = M.Prod
        AND T.Supp = M.Supp
        AND T.MaxDate = M.Date

Kristen

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-28 : 12:02:18

Thank you Kristen. It works as intended. I'm surprised that SQL doesn't have a TOP 1 or DISTINCT command that would return records by indicated groups.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources