How select the last product of each client

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

How select the last product of each client

Author

Topic

Flaterik
Starting Member

26 Posts

Posted - 2012-12-18 : 04:59:12

Hi people i have a table with this data

Cd_CF Cd_Ar Datadoc Price

For Example

Cd_CF Cd_Ar Datadoc Price
A0000 C1111 13/12/2012 231
A0000 C1111 13/11/2012 231
A0000 C1111 21/12/2012 90
A0000 C3333 21/12/2012 96
A0010 C1111 13/12/2012 65
A0010 B2222 13/12/2012 78

What i must have is
For Each CD_CF , the CD_AR with MAX Datadoc. But not only 1 Cd-AR , all the CD_ar but if is duplicated, only one with max datadoc.
Thanks

************************************************
the world is strange but people are crazy

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 05:18:18

SELECT Cd_CF, Cd_Ar, MAX(datadoc)
FROM @tab1
GROUP BY Cd_CF, Cd_Ar

If you want to display last product details along with price per each client,

SELECT t1.* FROM @tab1 t1
JOIN (SELECT Cd_CF, Cd_Ar, MAX(datadoc) maxx
FROM @tab1
GROUP BY Cd_CF, Cd_Ar) t2
ON t1.Cd_Ar = t2.Cd_Ar AND t1.Cd_CF = t2.Cd_CF AND t1.Datadoc = t2.maxx

--
Chandu