How to get just the top values when there is a tie in postgresql pgadmin4-CodePudding

I am having difficulty figuring out this dang problem. Can a postgresql pgadmin4 expert please assist? From the data and queries I have given below I am trying to see the email address that has rented the most movies during the month of September.

There are only 4 relevant tables in my database and they have been anonymized and shortened:

Table: cust         
Fields: cust_id | f_name | l_name | email
1 | Jack | Daniels | [email protected]
2 | Jose | Quervo | [email protected]
5 | Jim | Beam | [email protected]

Table: rent     
Fields: inv_id | cust_id | rent_date
10 | 1 | 9/1/2022 10:29
11 | 1 | 9/2/2022 18:16
12 | 1 | 9/2/2022 18:17
13 | 1 | 9/17/2022 17:34
14 | 1 | 9/19/2022 6:32
15 | 1 | 9/19/2022 6:33
16 | 3 | 9/1/2022 18:45
17 | 3 | 9/1/2022 18:46
18 | 3 | 9/2/2022 18:45
19 | 3 | 9/2/2022 18:46
20 | 3 | 9/17/2022 18:32
21 | 3 | 9/19/2022 22:12
10 | 2 | 9/19/2022 11:43
11 | 2 | 9/19/2022 11:42

Table: inv  
Fields: mov_id | inv_id
22 | 10
23 | 11
24 | 12
25 | 13
26 | 14
27 | 15
28 | 16
29 | 17
30 | 18
31 | 19
31 | 20
32 | 21

Table: mov      
Fields: mov_id | titl | rate
22 | Anaconda | 3.99
23 | Exorcist | 1.99
24 | Philadelphia | 3.99
25 | Quest | 1.99
26 | Sweden | 1.99
27 | Speed | 1.99
28 | Nemo | 1.99
29 | Zoolander | 5.99
30 | Truman | 5.99
31 | Patient | 1.99
32 | Racer | 3.99

and here is my current query progress:

SELECT cust.email,
count (DISTINCT inv.mov_id) AS "Rented_Count"
FROM cust
JOIN rent ON rent.cust_id = cust.cust_id
JOIN inv ON inv.inv_id = rent.inv_id
JOIN mov ON mov.mov_id = inv.mov_id
WHERE rent.rent_date BETWEEN '2022-09-01' AND '2022-09-31'
GROUP BY cust.email
ORDER BY "Rented_Count" DESC;

and here is what it outputs:

email |Rented_Count
[email protected] | 6
[email protected] | 6
[email protected] | 2

and what I want it to be outputting:

email
[email protected]
[email protected]

From the results I am actually getting I have a tie for first place (Jim and Jack) and that is fine but I would like it to list both tieing email addresses not just Jack's so you cant do anything with rows or max I don't think. I think it must have something to do with dense_rank but I am such a noob I don't know how to use that specifically in this scenario with the count and Group By? Your creativity and help would be appreciated. Thank you.

CodePudding user response：

Your missing the FETCH FIRST ROWS WITH TIES clause. It will work together with the ORDER BY clause to get you the highest values (FIRST ROWS), including ties (WITH TIES).

SELECT cust.email,
count (DISTINCT inv.mov_id) AS "Rented_Count"
FROM cust
JOIN rent ON rent.cust_id = cust.cust_id
JOIN inv ON inv.inv_id = rent.inv_id
JOIN mov ON mov.mov_id = inv.mov_id
WHERE rent.rent_date BETWEEN '2022-09-01' AND '2022-09-31'
GROUP BY cust.email
ORDER BY "Rented_Count" DESC
FETCH FIRST 1 ROWS WITH TIES

CodePudding user response：

This query will return the two email addresses that have rented the most movies during the month of September.

SELECT cust.email
FROM cust
JOIN rent ON rent.cust_id = cust.cust_id
JOIN inv ON inv.inv_id = rent.inv_id
JOIN mov ON mov.mov_id = inv.mov_id
WHERE rent.rent_date BETWEEN '2022-09-01' AND '2022-09-31'
GROUP BY cust.email
ORDER BY count(DISTINCT inv.mov_id) DESC
LIMIT 2;