问题描述:

I want to select data in my database and order it alphabetically but numbers first and then letters.

Sample of my table:

 watch_date

474 1442437 2181257 2015-12-20 Modern Family 3 13 Little Bo Bleep

475 1442437 2189485 2015-12-21 Modern Family 3 14 Me? Jealous?

476 1442437 2209159 2015-12-22 Modern Family 3 15 Aunt Mommy

477 1442437 2237415 2015-12-22 Modern Family 3 16 Virgin Territory

478 1442437 2244103 2015-12-22 Modern Family 3 17 Leap Day

479 1442437 2239817 2015-12-24 Modern Family 3 18 Send Out the Clowns

480 1442437 2305713 2015-12-24 Modern Family 3 19 Election Day

481 1442437 2305715 2016-01-02 Modern Family 3 20 The Last Walt

482 1442437 2247703 2016-01-03 Modern Family 3 21 Planes, Trains and Cars

483 1553656 2656658 unknown Under the Dome 1 1 Pilot

484 1553656 2779640 unknown Under the Dome 1 2 The Fire

485 1553656 2821206 unknown Under the Dome 1 3 Manhunt

I want to order it using the fourth column (the column with dates and the text 'unknown' (it's called 'watch_date'))

The data in this column is either the text unknown or a date with the format YYYY-MM-DD

To do this I use the following query:

SELECT * FROM `watched_episodes` ORDER BY `watch_date` + 0 DESC, `watch_date` DESC

I thought the + 0 part added a 0 to all results, yet when I do not add the second ORDER BY statement:

`watch_date` DESC

it does put the numbers (dates) before the letters but it does not seem to sort the dates at all.

网友答案:

Hmmm, your query is simply wrong. It should be giving an error because of the use of single quotes. I'll assume you intend:

SELECT *
FROM watched_episodes
ORDER BY watch_date + 0 DESC, watch_date DESC;

This seems like a strange method. If the values is always 'Unknown' or a string in YYYY-MM-DD format, I would go for:

ORDER BY (watch_date = 'unknown'),  -- USE `DESC` to put `unknown` first
         watch_date DESC
网友答案:

Use "union"

SELECT * FROM (
SELECT * FROM `watched_episodes` WHERE `watch_date` != 'unknown'
ORDER BY `watch_date` DESC
) table_alias
UNION SELECT * FROM `watched_episodes` WHERE `watch_date` = 'unknown';

You can order by whatever you want in subquery and still have the "unknown" rows after the actual date

Tip use "date" as column type of date format - so you can use date function without converting and just let the unknown be null.

相关阅读:
Top