What Could be the MySQL query to fetch the 3rd Maximum Value from a column in DB-table?

I was asked once,

What Could be the MySQL query to fetch the 3rd Maximum Value开发者_JAVA技巧 from a column in DB-table?

Please help me to have multiple way to do this.

Thanks

first way:

for nth highest column

   SELECT column FROM table ORDER BY column DESC LIMIT n-1, 1

Second way : ( eg : for N'th highest salary ) not tested

SELECT id FROM EMPLOYEE E1 WHERE 
 (N - 1) = (SELECT COUNT(distinct(SAL)) 
            FROM EMPLOYEE E2 
            WHERE E2.SAL > E1.SAL )

BASIC lOGIC:

You could sort the column into descending format and then just obtain the value from the nth row.

Row_Number

is the function will help you to get any record from your table.

WITH Sample AS
(
    SELECT id,
    ROW_NUMBER() OVER (ORDER BY idno) AS 'RowNumber'
    FROM TableName
) 

SELECT * 
FROM Sample 
WHERE RowNumber = 3;

SELECT columns FROM table ORDER BY column DESC LIMIT 2,1