Display the MAX record with JOIN

Question

I want to select the highest transaction amount and the corresponding Album Name to that amount.

This is what I have so far:

SELECT a.AlbumName, MAX(t.TransAmt) AS HighestSale
FROM TRANSACTIONS t 
JOIN COLLECTIONS c ON c.CollectionID = t.CollectionID
JOIN ALBUMS a ON a.AlbumID = c.AlbumID
GROUP BY a.AlbumName

I know I need a HAVING in there but I am unsure how to implement it.

This is what I get currently:

AlbumName	HighestSale
Cassadaga	10.00
I Walk The Line	13.00
Sweet Revenge	14.00
The Tree of Forgiveness	12.00

I only want to see the HighestSale (14.00) and then the AlbumName (Sweet Revenge)

Answer 1

You can do this using ORDER BY and fetching one row. In Standard SQL"

SELECT a.AlbumName, t.TransAmt AS HighestSale
FROM TRANSACTIONS t JOIN
     COLLECTIONS c
     ON c.CollectionID = t.CollectionID JOIN
     ALBUMS a
     ON a.AlbumID = c.AlbumID
ORDER BY t.TransAmt DESC
OFFSET 0 ROW FETCH FIRST 1 ROW ONLY;

Some databases don't support the standard FETCH clause, so you might want LIMIT or SELECT TOP (1) or something else.

Answer 2

This can work also

SELECT a.AlbumName, MAX(t.TransAmt) AS 
HighestSale
FROM TRANSACTIONS t 
JOIN COLLECTIONS c ON c.CollectionID = 
t.CollectionID
JOIN ALBUMS a ON a.AlbumID = c.AlbumID
WHERE t.TransAmt = (SELECT MAX(TransAmt) 
FROM TRANSACTIONS) 
GROUP BY a.AlbumName