select second largest from a table without limit

Question

How we can select the second largest mark or whatever from a table without using the LIMIT ? I know it is possible using LIMIT, but is it possible without using that?

suppose we have the columns id and marks.

Answer 1

Assuming marks is unique, following query gives you the second largest mark.

SELECT   MAX(marks)
FROM     ATable
WHERE    marks < (SELECT MAX(marks) FROM ATable)

To get the entire record, you could wrap this in an INNER JOIN

SELECT  t1.*
FROM    ATable t1
        INNER JOIN (
          SELECT   marks = MAX(marks)
          FROM     ATable
          WHERE    marks < (SELECT MAX(marks) FROM ATable)
        ) t2 ON t2. marks = t1.marks

Answer 2

select max(number), id 
from <tableName>
where number < (select max(number) from <tableName>)

Answer 3

You could do a

SELECT MAX(marks) FROM TABLE 
WHERE marks NOT IN (SELECT MAX(marks) FROM TABLE)

but LIMIT should perform better then the above, so the question is why you don't like it?

Answer 4

If you need whole row (all columns), this one will do it. In addition, it will always return only one row, even if there are several with the same 2nd max value.

Select top 1 * 
  From (Select Top 2 * 
          From TABLE 
         Order By marks desc
       ) a
Order By marks asc

If you want only one row with real 2nd max value, you should use:

select Top 1 * 
  from TABLE
 where marks < (select max(marks) from TABLE)
Order by max desc

It could be done by CTE (SQL Server 2005+) too:

;With a as 
(
Select Dense_Rank() over (order by marks desc) as nRank,
       *
  From TABLE
)
Select Top 1 * 
  from a
 Where nRank=2

If you want to see all rows with the 2nd max marks, just remove TOP 1 from previous query.