I am having difficulty figuring out this dang problem. 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":

Table "rent"

Table "inv"

Table "mov":

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:

and what I want it to be outputting:

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 don't know how to use that specifically in this scenario with the count and Group By?

Your creativity and help would be appreciated.

CodePudding user response：

You're 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
FROM cust
INNER JOIN rent 
        ON rent.cust_id = cust.cust_id
INNER JOIN inv 
        ON inv.inv_id = rent.inv_id
INNER 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
FETCH FIRST 1 ROWS WITH TIES