Sql query to fetch most recent record upto 10 and order by filed where row rank is mentioned

Question

Following is my table in Mysql. I want to fetch record of latest date in field "rank_date" and display result by order 1 to 10 based on order # in filed "drama_rank"

Answer 1

You can filter with a subquery, then order by:

select t.*
from mytable t
where rank_date = (select max(rank_date) from mytable)
order by drama_rank

Alternatively, assuming MySQL 8.0, you can use window functions:

select *
from (
    select t.*, rank() over(order by rank_date desc) rn
    from mytable t
) t
where rn = 1
order by drama_rank