MySQL: Select top n max values?

Question

I am really confused about the query that needing to return top N rows having biggest values on particular column.

For example, if the rows N-1, N, N + 1 have same values. Must I return just top N or top N + 1 rows.

question from:https://stackoverflow.com/questions/65845686/query-top-three-highest-scoring-students-in-a-subject-with-student-id-and-name

Answer 1

If you do:

select *
from t
order by value desc
limit N

You will get the top N rows.

If you do:

select *
from t join
     (select min(value) as cutoff
      from (select value
            from t
            order by value
            limit N
           ) tlim
    ) tlim
    on t.value >= tlim;

Or you could phrase this a bit more simply as:

select *
from t join
     (select value
      from t
      order by value
      limit N
    ) tlim
    on t.value = tlim.value;

The following is conceptually what you want to do, but it might not work in MySQL:

select *
from t
where t.value >= ANY (select value from t order by value limit N)