max date (complete brain fart)

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

SQL Server 2000 Forums

Transact-SQL (2000)

max date (complete brain fart)

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2009-05-20 : 16:01:56

I am having a complete brain fart on this one:
following query produces a set of results which includes some identical part numbers purchased at various dates (and possibly different prices)

select pomast.forddate,poitem.fpartno,poitem.fucost
from pomast
join poitem on pomast.fpono=poitem.fpono
where fcompany like 'CompanyX%'

all i want to do is get the latest fucost (based on max(pomast.forddate)) for each affected partnumber.

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 17:40:48

One way to do it:

select mast.forddate, item.fpartno, item.fucost
from pomast mast
join poitem item on mast.fpono=item.fpono
where fcompany like 'CompanyX%' 
and exists(select top 1 * from pomast where fpono=mast.fpono and forddate=mast.forddate order by forddate desc)

Warning: not tested.

Someone will probably post a CROSS APPLY and ROW_NUMBER() version that I really should learn how to do someday, so if my code doesn't work don't lose hope.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 20:53:52

quote:
Someone will probably post a CROSS APPLY and ROW_NUMBER() version

Nah . . . not in a SQL 2000 forum

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources