select distinct item where date is the highest

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 distinct item where date is the highest

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-09-18 : 23:13:40

hey guys,
so i have the following query

select itemcode, purchaseorder,date, supplier from invoice

that returns the following:
itemcode, purchaseorder, date, supplier
aaa12, 000011, 2012-09-11, XUANFOOD
aaa12, 000018, 2012-09-18, CHOWFOOD
aaa13, 000013, 2012-09-11, MISSFOOD
aaa14, 000011, 2012-09-11, XUANFOOD
aaa14, 000054, 2012-09-17, TERMFOOD

I only want to return one specific itemcode and one supplie. But the supplier that needs to be returned must be the one whose date is the latest date.

eg of expected results:
aaa12, 000018, 2012-09-18, CHOWFOOD
aaa13, 000013, 2012-09-11, MISSFOOD
aaa14, 000054, 2012-09-17, TERMFOOD

Hope that makes sense.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 23:42:51

[code]
select *
from
(
select *, rn = row_number() over partition by (itemcode order by date desc)
from invoice
) d
where rn = 1
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 11:50:02

in case compatibility level below 90 use below


select i.*
from invoice i 
inner join (select itemcode,max([date]) as latest
            from invoice
            group by itemcode
           )it
on it.itemcode = i.itemcode
and it.latest = i.[date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-09-20 : 22:30:24

thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:17:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources