Limit the rows for a column of a table

Question

I have a table like

MyTable (id, name, counter, createDate)

I can order the query like

Select * from MyTable order by counter;

I am just wondering, is it possible to limit the number of rows for each day. For example, I want the rows from the above query but I do not want them more than 10 rows from each day. So if it has 100 days of entry, I will be looking for less or equal to 10(row)x100(day) rows.

I am really not that good at SQL. Any help/suggestion would be really appreciatable.

is createDate the date you mean? is it a date or a datetime? — ysth, Dec 15 '20 at 08:18
Does this answer your question? [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results). I hope you can manage `datetime -> date` conversion with [reference to docs](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date). — astentx, Dec 15 '20 at 08:36

score 1 · Answer 1 · answered Dec 15 '20 at 08:39

10 rows per day with the least counter:

WITH cte AS ( SELECT *, ROW_NUMBER OVER (PARTITION BY createDate 
                                         ORDER BY counter) rn
              FROM MyTable )
SELECT *
FROM cte
WHERE rn <= 10

If createDate has DATETIME/TIMESTAMP datatype then

WITH cte AS ( SELECT *, ROW_NUMBER OVER (PARTITION BY DATE(createDate) 
                                         ORDER BY counter) rn
              FROM MyTable )
SELECT *
FROM cte
WHERE rn <= 10

score 1 · Answer 2 · answered Dec 15 '20 at 08:40

1

SELECT * FROM (SELECT row_number() OVER (PARTITION BY createDate ORDER BY createDate) r, c.* FROM MyTable T c) WHERE r <= 10

answered Dec 15 '20 at 08:40

Vahid

354
1
3
10

Limit the rows for a column of a table

2 Answers2