Obtain the second highest value

1

The problem I have solved, but here I expose my solution in case there is one better. (The following is an example, I will not put the actual tables) I have a table with routes and distances. It is desired to obtain the record with the second highest value. Be the table, Routes:

ruta  km
1     20
2     200
3     3000
4     500
5     400

With a

SELECT max(km) FROM rutas

I get the biggest distance, in this case 5000. Now nesting queries, if I do:

SELECT max(km) FROM rutas WHERE km < (SELECT max(km) FROM rutas)

I get the second largest distance, 500, but if I want to get all the fields of that record and I include the ruta field:

SELECT ruta, max(km) FROM rutas WHERE km < (SELECT max(km) FROM rutas)

I get the first value as a route, in this case 1. The form I found to obtain all the fields goes through:

SELECT ruta, km 
FROM rutas
WHERE km < (SELECT max(km) FROM rutas)
ORDER BY km DESC
LIMIT 1

With what I get what I was looking for 4, 500.

Any other solution?

    
asked by JLPrieto 20.03.2018 в 22:18
source

0 answers