Select artist.name and release.number

MusicBrainz Development
,
1

Hi every there, I find some help please for this query:
“Considering the average number of tracks among the releases published on CD, get the artists who have released only releases with more tracks than the average (the result must contain the artist’s name and release number and be sorted by descending release number) (write two versions of the query).”
Here is the answer but it does not work perfectly.

SELECT DISTINCT(A.NAME), R.id, R.id FROM track AS T
JOIN medium AS M ON T.MEDIUM = M.ID
JOIN medium_format AS MF ON M.FORMAT = MF.ID
JOIN "release" AS R ON M.RELEASE = R.ID
JOIN artist_credit AS AC ON R.ARTIST_CREDIT = AC.ID
JOIN artist_credit_name AS ACN ON AC.ID = ACN.ARTIST
JOIN artist AS A ON ACN.ARTIST = A.ID
WHERE MF.NAME LIKE 'CD' AND T.POSITION > ALL (SELECT AVG(T.POSITION) FROM TRACK AS T)
ORDER BY A.NAME DESC
2

Hi and welcome to MusicBrainz,

I don’t think people can really help you without more details about what “not perfectly” mans.

Without running your query but just looking at the code I have a few questions:

  • why do you have twice “R.id” in your select?
  • do you really need the artist_credit_name table?
  • are you sure your ORDER BY corresponds to the question?
  • I’m not sure your “MF.NAME LIKE CD” does what you thin it does
  • using T.POSITION looks expensive, don’t you have a cheaper way to do it?
  • are you sure your subquery “SELECT AVG…” returns what you expect?

I won’t say more because this looks like a school assignment :stuck_out_tongue:
(if it’s actually the case tell us where it’s from, we’re interested)

2 Likes