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