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)

Tag your question with the database you are using. – Gordon Linoff Jul 18 '21 at 18:36 — Gordon Linoff, Jul 18 '21 at 18:36

Gordon Linoff · Accepted Answer · 2021-07-18T18:46:40.973

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.

edited Jul 18 '21 at 18:46

answered Jul 18 '21 at 18:36

Gordon Linoff

1,242,037
58
646
786

Thanks! I was just about to respond and say I was getting this: "Invalid usage of the option FIRST in the FETCH statement." But I saw your update. SELECT TOP 1 is working. I am using MS SQL. Thanks again – nathan Jul 18 '21 at 18:41
@nathan, for MS SQL questions, you should add the tag also. – jarlh Jul 18 '21 at 18:41
@jarlh oops okay thanks, new user here – nathan Jul 18 '21 at 18:43
@Gordon Linoff my follow up question is what if I have two albums with the same price and they are max. I would want my query to show both. – nathan Jul 18 '21 at 18:53
1

@nathan SQL Server supports `TOP (1) WITH TIES` – Charlieface Jul 18 '21 at 19:15

score 0 · Answer 2 · answered Jul 18 '21 at 19:20

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

Display the MAX record with JOIN

2 Answers2