How to obtain all distinct items but limit each distinct to max n and ordered by date?

Question

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?

Answer 1

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;