Search code examples
mysqlsqlselectlimit

mysql query select limit when insert date column less than 10

I need a MYSQL query to select only latest 10 records order by insertDate, but I can't fix limit 10 because maybe some record have had same date and I want all column data in latest 10 distinct insertDate rows.

Solution

  • Use a sub-query to find 10'th latest insertDate:

    select * from tablename
where insertDate >= (select DISTINCT insertDate from tablename
                     order by insertDate desc limit 9,1)
order by insertDate desc

    I'm not sure if you want that DISTINCT in the sub-select or not. (I guess not...)