SQL select query order by day and month

Question

Let's pretend today is the 3rd of February.

And I have a table:

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

And I have 4 records:

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 would return all records in the following order (ordered by date, but upcoming dates first, passed dates appended to 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 records have the same year. In fact, year is not important, only day and month are. If you can suggest a better structure, you are very welcome.

Probably using a union of two queries with specific sort orders and where clauses — Resh32, Feb 01 '13 at 16:28
@Lamak thanks. Both Futures & Past dates `ASC`...interesting. +1 for the question. — bonCodigo, Feb 01 '13 at 16:36

score 4 · Accepted Answer · answered Feb 01 '13 at 16:30

4

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

will give order of 3,4,1,2

answered Feb 01 '13 at 16:30

paul

21,653
1
53
54

Can you add select clause as well please. – Dziamid Feb 01 '13 at 16:37
Wow, this really works, just adding a reference to the docs here: http://www.postgresql.org/docs/8.4/static/functions-conditional.html – Dziamid Feb 01 '13 at 16:47

score 2 · Answer 2 · answered Feb 01 '13 at 17:17

2

Simpler:

ORDER BY (date1 < now()), date1

You can just order by the boolean value of the expression (date1 < now()).
FALSE sorts before TRUE sorts before NULL.

answered Feb 01 '13 at 17:17

Erwin Brandstetter

605,456
145
1,078
1,228

score 1 · Answer 3 · answered Feb 01 '13 at 17:01

Here is another solution. Ofcourse above answer is good enough. You can't assume today is Feb 3rd since we are taking Now() which stand for Feb 2nd. So I used demo data 2013-02-01. And this answer is mainly depending on your ID. To say that ID is sequential so is the date. Anyone is free to comment on the dodgy 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 |

score 0 · Answer 4 · answered Feb 01 '13 at 16:31

0

If you were on MySQL i'd say just use RIGHT, but the 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)

answered Feb 01 '13 at 16:31

Mike Mackintosh

13,917
6
60
87

SQL select query order by day and month

4 Answers4

Linked