SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query with Max Value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

A_dil88
Starting Member

5 Posts

Posted - 12/11/2012 :  15:22:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  15:32:58  Show Profile  Reply with Quote
SELECT MAx(id),pid,MAX(date)
FROM test1
GROUP BY pid


Or
If you ID is not identity column

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

Edited by - sodeep on 12/11/2012 15:34:11
Go to Top of Page

A_dil88
Starting Member

5 Posts

Posted - 12/11/2012 :  16:53:14  Show Profile  Reply with Quote
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,
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  17:19:13  Show Profile  Reply with Quote
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

Edited by - sodeep on 12/11/2012 17:19:57
Go to Top of Page

A_dil88
Starting Member

5 Posts

Posted - 12/11/2012 :  19:07:19  Show Profile  Reply with Quote
Thank You for Your Reply. This Question is Answered. See above Sodeep last Comments..

Thank You.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000