1

I have a table where I have uuid as PK, command_name string, count_successful integer and command_start date.

I can grab all records by:

select 
    uuid, 
    command_name, 
    count_successful, 
    command_start 
from 
    sync_logs 
order by 
    command_start desc;

But above query will give me for example 78 records where command_name equals to foo and 45 records with command_name equals to bar.

How can I get max of 10 recent records per command_name?

2
  • See the added tag. – Rick James Dec 24 '18 at 1:44
  • 1
    Specify MySQL version, in this case it is important. – Akina Dec 24 '18 at 5:42
0

Apparently below query solves the issue and gives me recent 10 records for each command_name ordered by command name and when it started.

select
    s1.* 
from
    sync_logs as s1
left join 
    sync_logs as s2 
on 
    s1.command_name = s2.command_name and 
    s1.command_start <= s2.command_start
group by 
    s1.uuid
having 
    count(*) <= 10
order by 
    command_name asc, 
    command_start desc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.