How to sort MYSQL query to display alphabetically, with numbers last, and not including the word "The" in sort

Basically I have a PHP MYSQL query which returns 16 items. 15 of them start with letters A-Z, and one starts with a number (the name of a product is "3d Star Gazer"). In a regular query, sorting alphabetically displays this one item that starts numerically first. I want the list to be sorted alphabetically, with the 3d Star Gazer displayed last.

Is this possible?

SELECT t1.id, t2.name, t2.manufacturer
FROM db.t1
  LEFT JOIN db.t2 ON t1.id = t2.id
WHERE year = '2011'
ORDER BY t2.name ASC

I saw this link: Sort MySQL results alphabetically, but with numbers last but the solution wasn't working for me...

As a secondary question, i开发者_开发百科s it possible to not include the word "The" in the sort? For example, a game is called "The Guessing Game," I want it to appear sorted as "G", not as "T".

Using the IF() operator and adding two columns to control the sort order:

SELECT t1.id, t2.name, t2.manufacturer,
   IF(LEFT(t2.name, 1) BETWEEN '0' AND '9', 2, 1) as sortOrder1,
   IF(t2.name LIKE 'The _%', SUBSTRING(t2.name FROM  5), t2.name) as trimmedName
FROM db.t1
   LEFT JOIN db.t2 ON t1.id = t2.id
WHERE year = '2011'
ORDER BY sortOrder1, trimmedName;

The underscore in the pattern for the LIKE operator matches any single character, and is used to make sure that the returned substring will not be null.

If you don't want the extra columns in your final output, wrap the above select statement inside another:

SELECT id, name, manufacturer FROM ([select statement above]) a;

See the results in this answer

Sort MySQL results alphabetically, but with numbers last

with particular attention to the order by case

As for the extraction of "the", I'm sure you could do something in the lines of

 trim( replace( name, 'The','') ) as name_order

and then order based on that in conjunction with the order by case mentioned in the linked answer.

you can make an union first you select the values that start with letters and in the second query you select the ones that start with numbers

(select .... order by <<letter>>) union (select ... order by <<number>>)