Is it possible to get only 1 row after ordering? I don't want to load DB so much, so i want to get only 1 row (it may be from middle), here is example:

I want to get the fe. second user after ORDER BY `points` DESC, so the table will look like

The second user is user3, and i want to return just that one row. It is possible? Or do I need to get all and just LIMIT it?

CodePudding user response：

If you want to strictly get only one row, corresponding to the second highest points, then my guess is that on ties you want the lowest id. In that case you can use the LIMIT function accordingly.

SELECT * 
FROM tab
ORDER BY points DESC, 
         ID
LIMIT 1,1

The so written LIMIT clause will allow you to start limiting from the second row, then takes the first row available (which is the second one).

Check the demo here.

CodePudding user response：

If you are using MySql 8 you can use rank, for example:

with r as (
    select * , Rank() over(order by points desc) rnk
    from t
)
select id, user, points
from r
where rnk = 2;