6

Long-time reader, first-time poster here.

I'm trying to figure out how to sort a list of artists for a music app I'm writing.

To help understand the database structure: Rather than having a relational system where each song in the songs table has an artist ID that references a row in the artists table, I simply have a list of songs with the artist's name as a string in a column. I then use GROUP BY artist in a MySQL query to return a list of individual artists.

My app retrieves this data from my server in the form of a JSON-encoded array which is the result of the following MySQL query:

SELECT artist FROM songs GROUP BY artist ORDER BY artist ASC

However, this query results with artists with names like &i, +NURSE, and 2007excalibur2007 being sorted before the alphabetical results (such as AcousticBrony, ClaireAnneCarr, d.notive, etc.).

What I need is the artists whose names begin with numbers and symbols returned after the alphabetically-sorted artist list.

The solution can be PHP-based, but I'd prefer the elegance of it being done in the MySQL query.

Jacob Pritchett
  • 380
  • 1
  • 5
  • 14
  • 1
    "Rather than having a relational system where each song in the songs table has an artist ID that references a row in the artists table, I simply have a list of songs with the artist's name as a string in a column." Why? – Ignacio Vazquez-Abrams Jul 25 '12 at 01:11
  • AFAIK it's not possible. Theoretically you can create a custom collation, but it's not trivial: http://forge.mysql.com/wiki/How_to_Add_a_Collation – anttix Jul 25 '12 at 01:15
  • @IgnacioVazquez-Abrams Normally I most definitely would do this, but this table is frequently flushed and rebuilt automatically by another script of mine, one that reads the ID3 tags of several hundred music files and enters those details into rows in the table one by one. This way, new, modified, and deleted files are automatically accounted for. It would definitely be possible to make them as two separate tables, but this project's scope simply doesn't call for that. It'd over-complexify it, actually. – Jacob Pritchett Jul 25 '12 at 01:19
  • 1
    Nice job with first question! – O. Jones Jul 25 '12 at 01:32
  • Jacob, I think Ollie Jones solution does what you need and not Mark's despite the up-votes. I tried his suggestion similar to my CAST and it elegantly orders the data per your requirement using only MySQL and no extra table or PHP parsing. – Mike S. Jul 25 '12 at 01:39
  • 1
    @MikeS.: What's wrong with my answer? I posted the almost the exact same solution as Ollie. – Mark Byers Jul 25 '12 at 01:43
  • @MikeS. Mark's works for me, and was the first one I tried. But I tried Ollie's and it gives exactly the same results, even though the code is different. Why do `ORDER BY artist REGEXP '^[a-z]' DESC, artist` and `ORDER BY artist REGEXP '^[^A-Za-z]' ASC, artist` do the same thing? – Jacob Pritchett Jul 25 '12 at 01:46
  • 2
    @JacobPritchett: REGEXP returns 0 or 1. `REGEXP '^[a-z]'` returns 1 if the name starts with a letter, otherwise 0. This is then sorted in descending order so 1 comes first. `REGEXP '^[^A-Za-z]'` returns 1 if it *doesn't* start with a letter, but because it's ascending order the 1s come last. – Mark Byers Jul 25 '12 at 01:53
  • @Mark sorry I missed the first part of your answer somehow and just the part suggesting the additional column, thus requiring the extra work. I'll +1 yours too. ;-) – Mike S. Jul 25 '12 at 01:54

4 Answers4

13

This will put all the artists who's names begin with a letter in a-z before those that don't:

SELECT DISTINCT artist
FROM songs
ORDER BY artist REGEXP '^[a-z]' DESC, artist

See it working online: sqlfiddle

But you might prefer to store a second column with the simplified name so that you can put them in an order that makes more sense:

artists

artist            | simplified_name
------------------------------------
&i                | i
+NURSE            | nurse
2007excalibur2007 | excalibur

The values for simplified_name cannot be easily generated in MySQL, so you may want to use a general purpose programming language to pull out all the artists, transform them to simplified names, then populate the database with the results.

Once this is done, you can use this query:

SELECT DISTINCT artist
FROM artists
ORDER BY simplified_name
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
3

You can add an extra ORDER BY clause that puts the items that start with a non-alphabetic character last, like so:

    SELECT artist
      FROM songs
  ORDER BY artist REGEXP '^[^A-Za-z]' ASC, artist

This should move every artist that doesn't start with A-Z or a-z to the end of your ordering.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • This is best answer as much as I hate to admit it.. +1 (nice technique Ollie) – Mike S. Jul 25 '12 at 01:40
  • Thanks, this seems to work as well. I'm curious as to why this is returning identical results to `ORDER BY artist REGEXP '^[a-z]' DESC, artist`. What's the difference between `^[a-z]` and `^[^A-Za-z]`, and why does it require changing the descending to ascending? – Jacob Pritchett Jul 25 '12 at 01:48
  • Never mind, got it: http://stackoverflow.com/questions/11641370/how-to-sort-mysql-results-with-letters-first-symbols-last#comment-15422240 – Jacob Pritchett Jul 25 '12 at 01:56
0
ORDER BY ASCII(SUBSTR(artist, 1, 1)) NOT BETWEEN 65 AND 122, artist

This will order all artists that start with an alphabetical character before non alphabetical.

Note that because of how ascii works [ \ ] & _ ` will be considered alphabetical. If this matters you can split it into two boolean expressions to do the upper and lower case letters separately.

Or maybe:

ORDER BY ASCII(UPPER(SUBSTR(artist, 1, 1))) NOT BETWEEN 65 AND 90, artist

Be aware that this will only work for ascii characters. Letters that are part of other character sets won't be recognized as such.

Ariel
  • 25,995
  • 5
  • 59
  • 69
0

if you want sort by symbol first

  • ★★★ Symbol ★★★
  • 101 Hair Care
  • Abc
  • Def

then use below query

ORDER BY artist REGEXP '^[^A-Za-z0-9]' DESC, artist ASC
rusly
  • 1,504
  • 6
  • 28
  • 62