Search code examples
mysqlsqllimit

Return 4th highest value

When I am trying to find fourth highest t2 with query

SELECT MAX(T2) 
FROM TST
WHERE T2 NOT IN
(
SELECT DISTINCT T2 
FROM TST 
ORDER BY T2
DESC LIMIT 3
);

it shows: 'This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Can anyone suggest solution to this or an alternate query for same?

Solution

  • You don't need the subquery. The LIMIT keyword can be given a starting offset.

    SELECT DISTINCT t2
FROM TST
ORDER BY t2 DESC
LIMIT 3, 1

    This means to return 1 row starting at offset 3 (offset is zero-based, so this is the 4th highest).