Second biggest number using sql query?

7 May 2014


I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from TableName



For example, I have the following records:

NumColumn

     1

     2

     33

     44

     100

     110

     180

If I use "select Max(NumColumn) from aTable", I will get the number: 180.

But I need to use SQL to the second biggest number: 110.

Answer1:

select col

from (
    select ROW_NUMBER() over (order by col asc) as 'rowNum', col
    from [table] ) withRowNum 
where rowNum = 2

Answer2:
SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)
Answer3:
select min(NumColumn) from TableName where NumColumn in (select TOP 2 (NumColumn) from TableName order by NumColumn desc)