SQL select in a month but when over three group in one day, only show all of data of first three group

Question

I have data like this. I want to search all of the data in november. and sort by date and group. but when over 3 groups in one day, then only show the first 3 groups.

Sorry, I'm a newborn in SQL. I don't know to select by groups.

Example Data

id(pkey)    group   name    date

1             1     A001    11/1
2             2     A002    11/1
3             3     A003    11/1
4             4     A004    11/1
5             5     A005    11/1
6             1     A006    11/1
7             2     A007    11/1
8             3     A008    11/1
9             4     A009    11/1
10            5     A010    11/1
11            1     A011    11/2
12            2     A012    11/2
13            3     A013    11/2
14            4     A014    11/2
15            5     A015    11/2
16            1     A016    11/2
17            2     A017    11/2
18            3     A018    11/2
19            4     A019    11/2
20            5     A020    11/2
…             …     …       …

Result like this.

group   name    date
1       A001    11/1
1       A006    11/1
2       A002    11/1
2       A007    11/1
3       A003    11/1
3       A008    11/1
1       A011    11/2
1       A016    11/2
2       A012    11/2
2       A017    11/2
3       A013    11/2
3       A018    11/2

Answer 1

You can use dense_rank():

select
    "group", name, date
from (
    select t.*, dense_rank() over(partition by date order by "group") rn
    from mytable t
) t
where rn <= 3
order by date, "group", id

Demo on DB Fiddle:

group | name | date
----: | :--- | :---
    1 | A001 | 11/1
    1 | A006 | 11/1
    2 | A002 | 11/1
    2 | A007 | 11/1
    3 | A003 | 11/1
    3 | A008 | 11/1
    1 | A011 | 11/2
    1 | A016 | 11/2
    2 | A012 | 11/2
    2 | A017 | 11/2
    3 | A013 | 11/2
    3 | A018 | 11/2

Answer 2

Use a subquery that returns the first 3 groups of each day with IN:

select t.* 
from tablename t
where t."group" in (
  select distinct "group" from tablename 
  where "date" = t."date" 
  order by "group" limit 3
)
order by t."date", t."group", t."name"

See the demo.
Results:

> id | group | name | date
> -: | ----: | :--- | :---
>  1 |     1 | A001 | 11/1
>  6 |     1 | A006 | 11/1
>  2 |     2 | A002 | 11/1
>  7 |     2 | A007 | 11/1
>  3 |     3 | A003 | 11/1
>  8 |     3 | A008 | 11/1
> 11 |     1 | A011 | 11/2
> 16 |     1 | A016 | 11/2
> 12 |     2 | A012 | 11/2
> 17 |     2 | A017 | 11/2
> 13 |     3 | A013 | 11/2
> 18 |     3 | A018 | 11/2