How to get records with max value from two columns in postgresql-CodePudding

I have table structure like below in postgressql Where one ticketid can have multiple priority and rating

If i want the list of all ticket ids who have max priority as 2 and max rating as 3. Then output should give t2 and t4

If i want the list of all ticket ids who have max priority as 3 and max rating as 3. Then output should give t1 only

I am not sure how to write a query for this

CodePudding user response：

You can use the HAVING keyword to filter an aggregate after grouping:

SELECT ticketid 
FROM ratings 
GROUP BY ticketid 
HAVING max(priority) = 2 
AND max(rating) = 3;
 ticketid
----------
 t4
 t2
(2 rows)