Search code examples
mysqlgroup-bylimit

SQL How to Limit Number of Groups with No Limit in Rows Per Group

SELECT type, text, date FROM tableA GROUP BY CAST(date AS DATE) ORDER BY date DESC, id DESC LIMIT 5

I want to be able to select all rows (most recent days first), but limit the number of previous days to a certain amount (i.e. last 5 days with rows).

So if I had TableA with 10 days with 100 records in each day and TableB with 100 days with 10 records in each day, the result will be TableA will have 500 results (5 days with 100 records each), and TableB will have 50 results (5 days with 10 records each).

Hope that makes sense.

Solution

  • Join with a subquery that returns the most recent 5 days.

    SELECT t1.type, t1.text, t2.date
FROM tableA AS t1
JOIN (SELECT DISTINCT DATE(date) AS date
      FROM tableA
      ORDER BY date DESC
      LIMIT 5) AS t2 ON DATE(t1.date) = t2.date
ORDER BY t2.date DESC, t1.id DESC