In MySQL, how to fetch limit values for all id's

January 13, 2022

SELECT selected_date, id, price1, price2 
from values 
WHERE id = 100 
order by 1 desc 
LIMIT 90

I am fetching top 90 values from table for each id.

If I want to get for multiple ID’s in one query and top 90 items for each id, How do I write the query?

This query is giving 90 items for the first id only.
Ideally I want 90+90=180 items for each of the id’s. How to get this??

SELECT selected_date, id, price1, price2 
from values 
WHERE id IN (100, 101) 
order by 1 desc 
LIMIT 90

This is giving me only 90 items from the first id.

version of MySQL: 8.0

>Solution :

If your MySql version supports window functions you could try the following

with t as (
    select selected_date, id, price1, price2,
    Row_Number() over(partition by id order by selected_date desc) rn
    from values 
    where id in (100, 101)
)
select selected_date, id, price1, price2
from t
where rn <= 90