viMaL viMaL -4 years ago 65
SQL Question

Select second largest from a table without limit

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download