CODEWITHSUNDEEP
sqlpostgresqljoinlimit
undecided000
undecided000

Reputation: 105

How to limit amount of rows for each value?

This is my sample data.


Date         Name    Subject         Importance    Location     Time      Rating
12/08/2020   David   Work            1             London       -         -
1/08/2020    David   Work            3             London       23.50     4
2/10/2018    David   Emails          3             New York     18.20     3
12/08/2020   George  Updates         2             New York     -         -
1/08/2019    George  New Appointments5             London       55.10     2
2/10/2019    David   Emails          3             Paris        18.58     3
8/09/2017    David   Emails          2             Paris        18.90     5

I need to be able to see tomorrows meetings and the 3 previous meetings I have had with each client. So will be sorted by name, then date with the entries limited per name to 3. Could someone please point me in the right direction to be able to do this?

Expected result would be

Date         Name    Subject         Importance    Location     Time      Rating
12/08/2020   David   Work            1             London       -         -
1/08/2020    David   Work            3             London       23.50     4
2/10/2018    David   Emails          3             New York     18.20     3
2/10/2019    David   Emails          3             Paris        18.58     - 
12/08/2020   George  Updates         2             New York     -         -
1/08/2019    George  New Appointments5             London       55.10     2

Upvotes: 2

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use window functions to count the number of meetings of the next three that are "tomorrow". And then some filtering:

select t.*
from (select t.*,
             count(*) filter (where date = current_date + interval '1 day') over
                 (partition by name
                  order by date
                  rows between 1 following and 3 following
                 ) as cnt_tomorrow
      from t
     ) t
where date = current_date + interval '1 day' or
      cnt_tomorrow > 0
order by name, date;

Here is a db<>fiddle.

Upvotes: 1

Related Questions