Select Maximum of 2 rows for each ID

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

Transact-SQL (2000)

Select Maximum of 2 rows for each ID

Author

Topic

veeruu
Starting Member

10 Posts

Posted - 2006-01-23 : 23:41:03

Hi,

I need a query to display .. MAX Two Reords for all IDs.
The tables allows same IDs(more than One) with diff. data with new UpdatedDate

This is My sample Data ...

CID CType StartDate ExpDate UpdatedDate
--- ---- -(MM/DD/YYYY)- ----------- ------------
1 B1 01/01/2004 03/03/2004 01/02/2004
1 OPT 03/06/2004 03/03/2005 03/10/2004
1 H1 03/01/2005 01/03/2008 03/04/2005
2 B1 10/01/2004 01/09/2005 10/04/2004
2 OPT 01/06/2005 01/03/2006 03/07/2006
2 H1 01/01/2006 01/03/2009 01/04/2006

I need a select query to display the result as follows..

1 H1 03/01/2005 01/03/2008 03/04/2005
2 OPT 10/06/2005 01/03/2006 10/07/2006
2 H1 01/01/2006 01/03/2009 01/04/2006

i.e. 2 MAX Updated records for each CID.
And display Second Record if diff. between
Second Exp.Date and Current Date is 25.

Plz Help Me ....

Thanks & Regards,
Veeru.

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-24 : 03:16:20

If I understood your question correctly then here's your answer


Select a.*, Null Date_diff
from xxxTest a
Inner Join
	(
	Select Min(CID) CID, CType
	from xxxTest
	Group by CType
	) b
on a.CID = b.CID
Union
Select a.*, DATEDIFF(day, ExpDate, getdate())
from xxxTest a
Inner Join
	(
	Select Min(CID) CID, CType
	from xxxTest
	Where CID not in
		(
		Select Min(CID)
		from xxxTest
		)
	Group by CType
	) b
on a.CID = b.CID
Group By a.CID, a.CType, StartDate, a.ExpDate, UpdatedDate
having DATEDIFF(day, a.ExpDate, getdate()) > 25
Order by a.CType, a.CID

Regards
N

If you want your computer to be faster then throw it out of the window.

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-24 : 03:19:31

Here's what my result set looked like:
1 B1 2004-01-01 2004-03-03 2004-01-02 NULL
2 B1 2004-10-01 2005-01-09 2004-10-04 380
1 H1 2005-03-01 2008-01-03 2005-03-04 NULL
1 OPT 2004-03-06 2005-03-03 2004-03-10 NULL

If you want your computer to be faster then throw it out of the window.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-24 : 04:00:43

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2.

Go with the flow & have fun! Else fight the flow

veeruu
Starting Member

10 Posts

Posted - 2006-01-25 : 06:15:03

hi Norwich,
Thanks alot for ur suggesionn..
I will work on ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources