How to find second maximum record

  • Pls 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 suggestion

  • Second maximum as ordered by what?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • order by a required.

    i need the result should be as i mentioned

  • Your question is very unclear.

    Maybe you mean something like.

    SELECTMAX(Val)

    FROMTable

    WHEREVal < (SELECT MAX(Val) FROM Table)



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • yuvach_74 (11/14/2012)


    order by a required.

    i need the result should be as i mentioned

    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...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Heres one solution, using a Row_Number() to select the second record, but it depends on the order by

    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

    The first one does a Descending sort the second does an ascending sort so you get different values.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply