Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

tm555
Starting Member

5 Posts

Posted - 2010-11-23 : 04:51:55

Hi,

I am trying to solve this query but i can't get the result can anybody
solve this query.

Table :

ID CutomerID Stock Date

1 C 5 12/11/2010

2 A 21 10/11/2010

3 B 18 21/11/2010

4 A 17 20/11/2010

5 A 8 22/11/2010

6 B 7 23/11/2010

In the above table data I need the latest stock take for customers and customerID should come to be unique.

The expected result should like

ID CutomerID Stock Date

1 C 5 12/11/2010

5 A 8 22/11/2010

6 B 7 23/11/2010

can anybody solve my problem.

thanks and regards
brite

hai_venkat
Starting Member

10 Posts

Posted - 2010-11-23 : 05:14:36

select max(Stockdate) [Stockdate],ID,CutomerID
from purchase
group by ID,CutomerID

Venkat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 09:54:08

it should be


select t.*
from yourtable t
inner join (select max(Stockdate) [Stockdate],CustomerID 
from purchase
group by CustomerID)t1
on t1.CustomerID = t.CustomerID
and t1. Stockdate = t.[Stock Date]


or 2005 specific solution

SELECT Stockdate,ID,CutomerID 
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Stockdate DESC) AS rn,Stockdate,ID,CutomerID 
FROM purchase
)t
WHERE rn=1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources