SQL 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 2008 Forums

Transact-SQL (2008)

SQL Query

Author

Topic

sandeep.kumar
Starting Member

7 Posts

Posted - 2011-09-12 : 03:24:55

Hi,

I have a sql table with following data:

ID PName Location Pdate
-------------------------
1 AA L1 15/05/2010
2 AA L2 06/02/2009
3 AA L2 06/01/2009
4 BB L2 25/09/2011
5 BB L2 06/02/2009
6 BB L2 06/01/2009
7 CC L1 03/04/2009
8 CC L2 02/02/2009
9 CC L2 17/07/2010

Now I want to return single record for each PName having Max(PDate)

ID PName Location Pdate
-------------------------
1 AA L1 15/05/2010
4 BB L2 25/09/2011
9 CC L2 17/07/2010

I tried using Group By and Having clause, but could not get the results.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-12 : 03:34:11

select id, pname, location, pdate from
(
select id, pname, location, pdate, row_number() over (partition by pname order by pdate desc) as recid from table1
) AS d
where recid = 1

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:10:13

also


SELECT s.ID,t.PName,s.Location,s.Pdate
FROM (SELECT DISTINCT PName FROM table)t
CROSS APPLY (SELECT TOP 1 ID,Location, Pdate
             FROM Table
             WHERE PName=t.PName
             ORDER BY Pdate DESC)s

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

sandeep.kumar
Starting Member

7 Posts

Posted - 2011-09-14 : 00:29:14

Thank you very much for your help. Your solution solved my problem.

Thanks again

quote:
Originally posted by SwePeso

select id, pname, location, pdate from
(
select id, pname, location, pdate, row_number() over (partition by pname order by pdate desc) as recid from table1
) AS d
where recid = 1

N 56°04'39.26"
E 12°55'05.63"

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-09-15 : 11:47:00

one more solution

select ID,PName,Location,Pdate from tab a where Pdate in
( select max(Pdate) from tab b where
b.PName = a.PName)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources