SQL selects the order of queries by day and month

Question

Suppose today is February 3rd.

And I have a table:

CREATE TABLE devotion
(
  id serial NOT NULL,
  date timestamp without time zone
}

And I have 4 entries:

id date
1  2013-01-01
2  2013-02-02
3  2013-03-03
4  2013-04-04

I want to build a select query that will return all records in the following order (sorted by date, but upcoming dates first, date is at the end of the list):

id date
3  2013-03-03 (upcoming dates first)
4  2013-04-04
1  2013-01-01 (passed dates appended to the end of the list)
2  2013-02-02

All entries have the same year. In fact, the year is not important, only the day and month. If you can offer a better structure, you are very pleased.

Answer 1

order by case when date1 > now() then 0 else 1 end case, date1

will give the order of 3,4,1,2

Answer 2

Simpler:

ORDER BY (date1 < now()), date1

You can simply sort by the logical value of the expression (date1 < now()).
FALSEsorts before TRUEsorts before NULL.

Answer 3

Here is another solution. Of course, the above answer is enough. You can’t accept today on February 3, since we charge Now()who stand for Feb 2nd. Therefore, I used demo data 2013-02-01. And this answer is mostly up to you ID. Say the identifier is sequential, so this is the date. Anyone can comment on the ingenious part of this logic .

MYSQL DEMO

select id, `date`,
case when `date` > Date_Format(Now(),'%Y-%m-%d')
then id-3
else id+2 end as x
from demo
order by x asc
;

| ID |                            DATE | X |
--------------------------------------------
|  3 |    March, 03 2013 00:00:00+0000 | 0 |
|  4 |    April, 04 2013 00:00:00+0000 | 1 |
|  1 |  January, 01 2013 00:00:00+0000 | 3 |
|  2 | February, 01 2013 00:00:00+0000 | 4 |

Answer 4

If you were on MySQL, I would say just use it RIGHT, but below should work on psql:

SELECT * FROM devotion ORDER BY substring(to_char( date, 'YYYY-MM-DD') from char_length(to_char( date, 'YYYY-MM-DD')) - 5)