proper way to sort sql columns

Question

I have a table columns that is something like this:

id     |   date
1      |   January 1 2014 12:00 AM
2      |   February 1 2014 12:00 AM
3      |   March 1 2014 12:00 AM
4      |   April 1 2014 12:00 AM
5      |   May 1 2014 12:00 AM
6      |   June 1 2014 12:00 AM
7      |   July 15 2014 12:00 AM
8      |   July 15 2014 1:00 PM
9      |   July 15 2014 2:00 PM
10     |   July 15 2014 3:00 PM

Since ID 1-6 is already passed, I want them to be sorted from the bottom, and the latest one that is upcoming should be sorted at the top.

Desired output:

id     |   date
7      |   July 15 2014 12:00 AM
8      |   July 15 2014 1:00 PM
9      |   July 15 2014 2:00 PM
10     |   July 15 2014 3:00 PM
6      |   June 1 2014 12:00 AM
5      |   May 1 2014 12:00 AM
4      |   April 1 2014 12:00 AM
3      |   March 1 2014 12:00 AM
2      |   February 1 2014 12:00 AM
1      |   January 1 2014 12:00 AM

Answer 1

Best way I can think of is to first sort on future/historic dates, then sort the historic dates reversed and the future dates normally.

Simply reversing the date by -1 doesn't work, because MySQL will not be able to handle date type in combination with number type to sort on. Therefore, in both case it needs to be converted to a number.

So, like this:

ORDER BY
    (CASE WEN date1 < NOW() THEN 1 ELSE 0 END),
    (CASE WHEN date1 < NOW() THEN -1 * date1 ELSE 1 * date1 END)

Fiddle, based on fiddle from VBlades: http://sqlfiddle.com/#!2/eb83b/17

Answer 2

Depending on your DBMS the date arithmetic is a bit differerent. The trick however is to use a case expression to map historical dates into the future. Something like:

order by date_col + case when date_col < now() then 100 else 0 end year

Answer 3

Here's a way to do it in SQL Server, but should be easily portable (http://sqlfiddle.com/#!3/89d35/24):

select
          id,
          date
from
  (select
            (select count(*) FROM table1 where id <= t1.id and date >= getdate()) as row,
            *,
            1 AS TableSort
  from
            table1 t1
  where
            t1.date >= getdate()

  union all

  select
            (select count(*) FROM table1 where id >= t2.id and date < getdate()) as row,
            *,
            2 AS TableSort
  from
            table1 t2
  where
            date < getdate()) t3
order by
          tablesort,
          row

Answer 4

Your problem is that you have essentially two queries here, with different ordering. If you're not able to actually run them as two queries (that is, in your application layer), you need to get fancy.

However:

• The problem with ORDER BY CASE... solutions is that they are (almost) guaranteed to never use an index - the optimizer can't page up/down through the index because the order is dependent on the row value.
• The same limitation as above applies to doing various types of "math" on the desired columns. Plus the additional overhead of whatever math you're doing.

If your result set is small, the above is a non-issue. On dbs of any real size, though, this is important. So...

When you have two (semi-related) queries to combine, you use UNION ALL (or UNION if there are duplicates to remove). The problem is that the resulting sets are stitched together in an "undefined" order (this is not always explicitly spelled out in documentation). Reasons for this vary, but will include if the system is using data parallelism, which side may currently be waiting for a lock, or just because it's Tuesday in Sunnydale. So we'll need an ORDER BY...

Except the result in your query essentially requires two (different) ORDER BYs. And we can only supply one for use with a UNION (ALL) operator (because the sort will be performed on the combined set). Fortunately, different ordering can be given for each column for the clause! And UNION would allow us to supply a "dummy" null column - so where does that sort results?

SELECT id, COALESCE(date, other) AS sortedDate
FROM (SELECT id, date, null AS other
      FROM <sourceTable>
      WHERE date >= CURRENT_TIMESTAMP
      UNION ALL
      SELECT id, null, date
      FROM <sourceTable>
      WHERE date < CURRENT_TIMESTAMP) Derived
ORDER BY date ASC, other DESC

PostgreSQL Fiddle Demo

Why does this work? In RDBMSs, nulls sort to one end of the range. For PostgreSQL, Oracle, and DB2, they are sorted as the "greatest" value. That is, an intermediate table that looks like this is generated:

id     |   date                       |  other
7      |   July 15 2014 12:00 AM      |  null
8      |   July 15 2014 1:00 PM       |  null
9      |   July 15 2014 2:00 PM       |  null
10     |   July 15 2014 3:00 PM       |  null
6      |   null                       |  June 1 2014 12:00 AM
5      |   null                       |  May 1 2014 12:00 AM
4      |   null                       |  April 1 2014 12:00 AM
3      |   null                       |  March 1 2014 12:00 AM
2      |   null                       |  February 1 2014 12:00 AM
1      |   null                       |  January 1 2014 12:00 AM

... you can see how this compares with the ordering expressed in the ORDER BY, right?

Unfortunately, for MySQL and SQL Server, null is the lowest value, so we have to change our statement!

SELECT id, COALESCE(date, other) AS sortedDate
FROM (SELECT id, 1 AS orderGroup, date, null AS other
      FROM <sourceTable>
      WHERE date >= CURRENT_DATE
      UNION ALL
      SELECT id, 2, null, date
      FROM <sourceTable>
      WHERE date < CURRENT_DATE) Derived
ORDER BY orderGroup ASC, date ASC, other DESC

SQL Server Fiddle Demo

... here, the intermediate table looks like this:

id   |  og  |  date                       |  other
7    |  1   |  July 15 2014 12:00 AM      |  null
8    |  1   |  July 15 2014 1:00 PM       |  null
9    |  1   |  July 15 2014 2:00 PM       |  null
10   |  1   |  July 15 2014 3:00 PM       |  null
6    |  2   |  null                       |  June 1 2014 12:00 AM
5    |  2   |  null                       |  May 1 2014 12:00 AM
4    |  2   |  null                       |  April 1 2014 12:00 AM
3    |  2   |  null                       |  March 1 2014 12:00 AM
2    |  2   |  null                       |  February 1 2014 12:00 AM
1    |  2   |  null                       |  January 1 2014 12:00 AM

(incidently, this version will work even when null sorts as "greatest")

So what about the ability for the db to use indices?

I don't know. (I don't have a good box/sample set to test on anyways...)

It's going to depend on how smart the optimizer is, and if it can extract the constants from the SELECT lists. If so, I expect that it's going to scan the relevant index twice - once ascending and once descending, from the start of the range.

Answer 5

Simple:

select  *
from test
order by case when date < now() then 1 else 0 end, date;