SQL - Two columns group by issue with MAX function

Question

SELECT artist.name, recording.name, MAX(recording.length)
FROM recording 
INNER JOIN (artist_credit 
            INNER JOIN (artist_credit_name 
                        INNER JOIN artist 
                                ON artist_credit_name.artist_credit=artist.id)
                    ON artist_credit_name.artist_credit=artist_credit.id)
        ON recording.artist_credit=artist_credit.id
WHERE artist.gender=1 
  AND recording.length <= (SELECT MAX(recording.length) FROM recording)
GROUP BY artist.name, recording.name
ORDER BY artist.name

We are using the MusicBrainz database for school and we are having troubles with the "GROUP BY" because we have two columns (it works with one column, but not two). We want the result to display just one artist with his second longest recording time, but the code displays all the recording time of every song of the same artist. Any suggestions? Thanks.

I would use window functions if it were TSQL to find the second longest recording for each artist. A web search will likely reveal how to accomplish the same with Postgre. — Joe C, May 29 '18 at 11:43
@HaleemurAli Because the data we need are from different tables. — Book Reader, May 29 '18 at 11:48
i see the need for joins, but i don't see any need to nest them as you have done. the query is equivalent if you remove all the brackets in the join clause — Haleemur Ali, May 29 '18 at 11:50
@JoeC We have already searched in the web for couple of hours and only found group by with single columns. The only solution with two columns was with a COUNT function but we can't use it. — Book Reader, May 29 '18 at 11:51
Could you show us example input data and example result that you want to get? I'm not really sure how there can be different length for recording with the same name by the same artist. — Łukasz Kamiński, May 29 '18 at 12:15

score 0 · Answer 1 · answered May 29 '18 at 12:52

0

You don't need to do multiple joins looking closely at the join conditions. They can be reduce to just one join as shown below.

SELECT DISTINCT B.name, A.name, A.length
FROM recording A JOIN artist B
ON A.artist_credit=B.id
WHERE B.gender=1
AND A.length=(SELECT C.length FROM recording C    
              WHERE C.artist_credit=B.artist_credit 
              ORDER BY C.length LIMIT 1, 1)
ORDER BY B.name;

See Using MySQL LIMIT to get the nth highest value

answered May 29 '18 at 12:52

cdaiga

4,861
3
22
42

I had to use multiple joins because i have a E/R table that i should follow(school stuff) btw LIMIT #,# isnt supported on postgres – Book Reader May 31 '18 at 08:03

score 0 · Answer 2 · answered May 29 '18 at 13:03

0

As others have pointed out, the join statement can be reduced. Also there seems to be a problem with the operator in the AND statement; it should be < and not <= in order to get the second highest length (Se here: What is the simplest SQL Query to find the second largest value?).

I would suggest trying out the following:

SELECT artist.name, recording.name, MAX(recording.length)
FROM recording 
JOIN artist ON recording.artist_credit = artist.id
WHERE 
    artist.gender=1 
    AND 
    recording.length < (SELECT MAX(recording.length) FROM recording)
GROUP BY artist.name
ORDER BY artist.name

answered May 29 '18 at 13:03

Benjamin Andersen

462
1
6
19

tried it on postgre, it gives an error because recording.name should be on the GROUP BY and if i put it on GROUP BY which is basically the same as my code – Book Reader May 31 '18 at 07:57
Please provide some mockup-data to your question, then it will be easier for us to understand your issue. – Benjamin Andersen May 31 '18 at 11:52

SQL - Two columns group by issue with MAX function

2 Answers2