CODEWITHSUNDEEP
mysql
OM The Eternity
OM The Eternity

Reputation: 16204

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 from a column in DB-table?

Please help me to have multiple way to do this.

Thanks

Upvotes: 0

Views: 268

Answers (3)

xkeshav
xkeshav

Reputation: 54016

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.

Upvotes: 5

Amit
Amit

Reputation: 22076

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

Upvotes: 0

Pankaj
Pankaj

Reputation: 10095

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;

Upvotes: 0

Related Questions