Query with Max Value - 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

General SQL Server Forums

New to SQL Server Programming

Query with Max Value

Author

Topic

A_dil88
Starting Member

5 Posts

Posted - 2012-12-11 : 15:22:09

Hi Guys,

Here is my sample Data,

Create Table Test1
(
ID INT,
DATE DATETIME,
PID INT
)

INSERT INTO Test1
values('1','2006-07-12 00:00:00.000','594820')
GO
INSERT INTO Test1
values('2','2007-07-12 00:00:00.000','594820')
GO
INSERT INTO Test1
values('3','2008-07-12 00:00:00.000','594820')
GO
INSERT INTO Test1
values('4','2012-07-12 00:00:00.000','594820')
GO

SELECT
id,MAX(date),pid FROM test1
GROUP BY id,pid

Result = ID,date,pid
4 2012-07-12 594820

I want Max date with id and pid.
Thanks in advance..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 15:32:58

[code]SELECT MAx(id),pid,MAX(date)
FROM test1
GROUP BY pid[/code]

Or
If you ID is not identity column

[code]Select i.* from Test1 i
inner join
(Select PID,MAX(Date)MAXDate
from Test1
Group by PID
)P on P.PID = i.PID and P.MAXDate = i.Date[/code]

A_dil88
Starting Member

5 Posts

Posted - 2012-12-11 : 16:53:14

Sodeep, Thank you for your Reply. Your Solution is working for Sample data that i provided, but not in my original Database.
I have Distince ID,max(Date),Duplicate(Pid)
I want Latest PID with Max(Date)
For Example (Sample Data Below)
ID,DATE,PID
1,12/10/2011,123
2,10/10/2011,123
3,12/10/2012,456

End Result Should be
ID,DATE,PID
1,12/10/2011,123
3,12/10/2012,456

ID (IS NOT IN SEQUENCE).

Thank You,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 17:19:13

Even other query should work

Select * from
(Select *,ROW_NUMBER()OVER(PARTITION BY PID ORDER BY DATE desc) Seq
from Test1
)P
Where P.Seq = 1

A_dil88
Starting Member

5 Posts

Posted - 2012-12-11 : 19:07:19

Thank You for Your Reply. This Question is Answered. See above Sodeep last Comments..

Thank You.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources