Mysql different sort orders

Question

Have a simple table of data that stores news and events with a date field (manual entry on this field). Just has a simple flag to tell what is a news article and what is an event.

What I need to do somehow is to get the news in date descending order so the latest news is displayed first, but I want events in ascending order so the oldest is displayed first, all the data should be listed as one list of a mix of news/events, but listed in the correct date order so the records will be mixed together

=====================================
Title          | Date         | Type
=====================================
News 1         | 2016/01/18   | N
News 2         | 2016/01/23   | N
Event 1        | 2016/01/25   | E
Event 2        | 2016/02/21   | E

Need the data to be output something like:

=====================================
Title          | Date         | Type
=====================================
News 2         | 2016/01/23   | N
Event 2        | 2016/01/21   | E
Event 1        | 2016/01/25   | E
News 1         | 2016/01/18   | N

Not the best example but hopefully enough to give you an idea. Not sure if this will even be possible.

Possible duplicate of order by multiple columns.http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering — rahul, Mar 01 '16 at 09:36
News and events are mixed as the information they store are almost identical apart from one or two fields, even as two separate tables still not sure if what I'm after is possible. Yes news and events are the only two options. — Trekmp, Mar 01 '16 at 09:49
look at substring_index to split this column into NEWS and NUMBER — , Mar 01 '16 at 09:53
Not sure this is even logical. For example, from your description Event 1 should come after Event 2 (as it has a later date) but it also should come before News 2 as it has a later date. — Kickstart, Mar 01 '16 at 14:11
ORDER BY date DESC,title for news and ORDER BY date,title for events. This is how I'd do it if I had two separate lists, but that's not what I'm after, though I think I'm going to have to go down the route of having 2 lists — Trekmp, Mar 01 '16 at 15:41

score 0 · Answer 1 · answered Mar 02 '16 at 10:32

I'm not sure how to recreate your expected result as "today" is likely to be different. All I can propose is that you need a calculation , perhaps based around curdate() so that future date are "penalized" in the sorting. For example this SQL Fiddle rune with curdate() = March, 02 2016 00:00:00

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`Title` varchar(7), `Date` datetime, `Type` varchar(1))
;

INSERT INTO Table1
    (`Title`, `Date`, `Type`)
VALUES
    ('Event 1', '2016-01-25 00:00:00', 'E'),
    ('Event 2', '2016-02-21 00:00:00', 'E'),
    ('News 1', '2016-01-18 00:00:00', 'N'),
    ('News 2', '2016-01-23 00:00:00', 'N')
;

Query 1:

select
      (case when `Date` > curdate() then `Date`-curdate() else curdate() - `Date` end) as scale
    , `Date`
    , `Title`
    , `Type`
from table1
order by scale

Results:

|           scale |                       Date |   Title | Type |
|-----------------|----------------------------|---------|------|
| -20160200839698 | February, 21 2016 00:00:00 | Event 2 |    E |
| -20160104839698 |  January, 25 2016 00:00:00 | Event 1 |    E |
| -20160102839698 |  January, 23 2016 00:00:00 |  News 2 |    N |
| -20160097839698 |  January, 18 2016 00:00:00 |  News 1 |    N |

I'll give this a try and thanks @Used_By_Already, never thought about looking at it that way — Trekmp, Mar 03 '16 at 11:17

Mysql different sort orders

1 Answers1