InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:51:44 GMT20http://www.sqlservercentral.com/Forums/Topic1384422-392-1.aspxHeres one solution, using a Row_Number() to select the second record, but it depends on the order by [code="sql"]Declare @t as Table (a int, b date)insert into @t (a,b)values('2','2012-12-21')insert into @t (a,b)values('2','2012-12-23')insert into @t (a,b)values('3','2012-11-23')insert into @t (a,b)values('3','2012-11-21')insert into @t (a,b)values('2','2012-12-21');With Cte as (Select *,Row_Number() Over(Partition by a order by b desc) rn from @t)Select a,b from Cte where rn=2;With Cte as (Select *,Row_Number() Over(Partition by a order by b asc) rn from @t)Select a,b from Cte where rn=2[/code]The first one does a Descending sort the second does an ascending sort so you get different values.Wed, 14 Nov 2012 01:30:08 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1384422-392-1.aspx[quote][b]yuvach_74 (11/14/2012)[/b][hr]order by a required.i need the result should be as i mentioned[/quote]With the sample data you gave, the second max row when ordered by a will not give the results you mentioned. The second max row ordered by a would return either '2','2012-12-21' or '2','2012-12-23'Select distinct a, min(b) from table group by a will give you what you asked, but I suspect it's not what you want.Perhaps a more detailed explanation and better sample data would help...Wed, 14 Nov 2012 01:21:12 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1384422-392-1.aspxYour question is very unclear.Maybe you mean something like.[code="sql"]SELECT MAX(Val)FROM TableWHERE Val < (SELECT MAX(Val) FROM Table)[/code]Wed, 14 Nov 2012 01:07:34 GMTDennis Posthttp://www.sqlservercentral.com/Forums/Topic1384422-392-1.aspxorder by a required.i need the result should be as i mentionedWed, 14 Nov 2012 01:02:16 GMTyuvach_74http://www.sqlservercentral.com/Forums/Topic1384422-392-1.aspxSecond maximum as ordered by what?Wed, 14 Nov 2012 00:57:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1384422-392-1.aspxPls give me a query for to select second maximum of the result set.a,b is column names.insert (a,b)values('2','2012-12-21')insert (a,b)values('2','2012-12-23')insert (a,b)values('3','2012-11-23')insert (a,b)values('3','2012-11-21')insert (a,b)values('2','2012-12-21')i need to get the result as'2','2012-12-21''3','2012-11-21'like that i need to get the result. pls give any suggestionWed, 14 Nov 2012 00:28:00 GMTyuvach_74