-1

How to produce this output in MySQL?

Sort the albums first based on the album format in descending order followed by the tracks in ascending order.

enter image description here

I can come out the output with this command:

SELECT album_singer, album_name, album_format_name, album_tracks
FROM album, album_format
WHERE album.Album_Type_ID = album_format.Album_Format_ID
ORDER BY album_format_name DESC, album_tracks ASC

Output that I can produce:

Vinyl
Vinyl
Digital
Compact Disc
Compact Disc
Compact Disc
Compact Disc
Compact Disc
Cassette
Cassette

Output that I need:

Vinyl
Vinyl
Digital
Cassette
Cassette
Compact Disc
Compact Disc
Compact Disc
Compact Disc
Compact Disc
Kingsley Yong
  • 83
  • 8

1 Answers1

0

MySQL's field is very useful for custom ordering like your expected output requires. Additionally, as Dale K mentioned in the comments, implicit joins (having more than one item in the from clause) is an outdated practice, and you should probably use an explicit join clause:

SELECT   album_singer, album_name, album_format_name, album_tracks
FROM     album
JOIN     album_format ON album.Album_Type_ID = album_format.Album_Format_ID
ORDER BY FIELD(album_format_name, 'Vinyl', 'Digital', 'Cassette', 'Compact Disc'), 
         album_tracks ASC
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    I learned from my lecturer who told me this way to write the command. Thank you to inform me that it was outdated. Really thanks a lot. – Kingsley Yong Sep 21 '20 at 09:33