Search code examples
mysqlheidisql

SQL: How to Order By the attributes in Descending but second character of it need to be Ascending Order

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
Solution

  • 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