Second max

• writeforcontact

  Newbie

  Points: 3

  More actions

  January 23, 2014 at 1:56 am

  #146640

  i have used a query to get the second max from a list of sal like this:-

  select sal from emptab where empid in (2,4,7) and rownum = 2 order by sal desc;

  the problem is that the rownum is not working, can anyone suggest how to get second max value from a list of values?

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  January 23, 2014 at 2:06 am

  #1682657

  I guess this is an Oracle query?

  In SQL Server you need to use the ROW_NUMBER function and sort in the OVER clause.

  In an outer query you can select the row with row number equal to 2.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP