tfcsantana tfcsantana - 4 months ago 12
SQL Question

How to get all rows with second highest value

I have this following table:

name value year
A 1 2015
A 2 2014
A 3 2013
B 1 2015
B 3 2013
C 1 2015
C 2 2014


How can I get, for each name, the row with the second highest year, like this:

name value year
A 2 2014
B 3 2013
C 2 2014


I tried the following query but no success:

select name, value, year
from TABLE_NAME
WHERE year IN (select year from TABLE_NAME order by year desc limit 1,1)


The previous query gives me this error:
"SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' "

And I can't change the MySQL version (5.6.25) right now, because the solution is already in production.

Any help, please?

Answer

One way to solve n per group in MySQL is to simulate ROW_NUMBER. Note that this will only return one value per name.

SELECT 
    name, 
    value,
    year
FROM 
    (SELECT 
        t.name, 
        t.value,
        t.year,
        @rn := if(@prev = t.name, @rn + 1,1) as rn,
        @prev:=t.name

    FROM
        test_table as t
        JOIN (SELECT @Prev:= Null, @Rn := 0) as v
    ORDER BY 
        t.name,
        T.year desc) as t
WHERE
    rn = 2;