select MAX Two Rows for all IDs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

select MAX Two Rows for all IDs

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 rows for each CID.
And display Second Record if diff. between
Second Exp.Date and Current Date is 25. Plz. Help Me…. Thanks & Regards,
Veeru.
try this: select t1.* from t1
inner join (select cid,max(expdate) mx from t1 group by cid) tmp
on t1.cid=tmp.cid and t1.expdate=tmp.mx
UNION
select t1.* from t1
where datediff(d,expdate,getdate())<=25
Ranjit, I think the first query should rather look like<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.*, <br /> FROM table t1<br /> WHERE t1.UpdatedDate IN<br /> (SELECT TOP 2 t2.UpdatedDate<br /> FROM tablet2<br /> WHERE t2.CID= t1.CID<br /> ORDER BY t2.UpdatedDate DESC)<br /></font id="code"></pre id="code"><br />or<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.*<br /> FROM table t1 <br /> WHERE <br /> (SELECT COUNT(*) <br /> FROM table<br /> WHERE UpdatedDate&lt;=t1.UpdatedDate AND CID = t1.CID) &lt;=2 <br /></font id="code"></pre id="code"><br />untested…[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
The question select MAX Two Rows for all IDs doesnt match with the expected result 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
Madhivanan Failing to plan is Planning to fail
Doh, in that case it might be better to post the usual DDL, sample data and expected results. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
]]>