Asad Mahmood Asad Mahmood - 2 days ago 3
SQL Question

How to find the single highest value? How to show everything if there is a tie?

I want to have the output of the movie(s) that have the most awards. The problem I'm having is how do I show a single movie? I tried to make a

PIVOT
function, and use the
MAX()
function rather than the
COUNT()
function; however I am would only get the output of 1 with almost all of the rows. I would however like to use the
MAX()
function to do this. I also want to know how can I show all "Movies" if there would be a tie? From my information there isn't going to be any tie, however if there were to be one, I would like it if all of the information would be shown.

Expected output:

MOVIE Awards Won
----------------------------------- ----------
Saving Private Ryan 6

1 rows selected


Output with my query:

MOVIE Awards Won
----------------------------------- ----------
A Lonely Place to Die 5
Act of Valor 0
Captain America: The First Avenger 2
Date Night 1
Drive Angry 0
Saving Private Ryan 6
Taken 1

7 rows selected


Here is my query:

SELECT * FROM
(
SELECT MovieTitle AS "MOVIE",
TBLAWARDRESULT.AWARDRESULTDESC AS "Result Type",
TBLAWARDRESULT.AWARDRESULTID AS "Rating"

FROM TBLMOVIE
INNER JOIN TBLAWARDDETAIL
ON TBLMOVIE.MOVIEID = TBLAWARDDETAIL.MOVIEID

INNER JOIN TBLAWARDRESULT
ON TBLAWARDDETAIL.AWARDRESULTID = TBLAWARDRESULT.AWARDRESULTID

ORDER BY Movietitle
)

PIVOT
(
COUNT("Rating") FOR "Result Type"
IN ('Won' AS "Awards Won")
)
ORDER BY Movie;


Tables:

File1 (PasteBin)

File2 (PasteBin)

vkp vkp
Answer

Use RANK function to order the results by award count descending, which would get you multiple rows in case of ties as well.

SELECT MOVIE,Awards_Won
FROM (
SELECT 
MovieTitle AS "MOVIE",
COUNT(TBLAWARDRESULT.AWARDRESULTID) AS Awards_Won,
RANK() OVER(ORDER BY COUNT(TBLAWARDRESULT.AWARDRESULTID) DESC) RNK
FROM TBLMOVIE
INNER JOIN TBLAWARDDETAIL ON TBLMOVIE.MOVIEID = TBLAWARDDETAIL.MOVIEID 
INNER JOIN TBLAWARDRESULT ON TBLAWARDDETAIL.AWARDRESULTID = TBLAWARDRESULT.AWARDRESULTID
WHERE TBLAWARDRESULT.AWARDRESULTDESC = 'Won'
) t
WHERE RNK = 1
Comments