How to order these SQL results in a certain way?

Question

I'm making a sort of calendar of events app. There are 2 types of events shown on the users home page:

nearby event - those with a schedule date within the next 3 days, or within the past 1 hour.
normal event - those with either no schedule date, or a schedule date further than 3 days in the future.

This is the events table: id, user_id, content, occurs_at, created_at

Currently, I'm showing these events ordered by date created to show the newly added stuff first.

What I want is to show all nearby events first and have them ordered by schedule date, then show the normal events and have them ordered by date created. I think it makes for a better user experience, but I don't know how to go about it.

UPDATE

Ended up going with this query, which is a combination of a couple of answers.

ORDER BY COALESCE(occurs_at > :min_time AND occurs_at < :max_time, 0) DESC, occurs_at ASC, created_at DESC

You can add the *fake* sort column to achieve this (Beth's answer). But as easily you can re-arrange the results of SA query in memory after the events have been retrieved. — van, Oct 04 '12 at 21:23

score 2 · Accepted Answer · answered Oct 04 '12 at 19:36

order by clauses can contain arbitrary logic, not just field names, so, in pseudo-code, you could have

SELECT ...
...
ORDER BY (date_diff(occurs_at, now) <= '3 days'), occurs_at DESC, created_at DESC

the first clauses forces all events that occur within the next 3 days to the top of the list. the second clause then orders those in descending order, then everything else gets sorted by the created_at date.

score 2 · Answer 2 · answered Oct 04 '12 at 20:23

2

As @MarcB mentions, you can order by an expression, although for PostgreSQL, the expression looks somewhat different;

SELECT * FROM Events
ORDER BY CASE WHEN (occurs_at - '3 days'::interval) < CURRENT_TIMESTAMP 
         THEN occurs_at
         ELSE '9999-01-01'::timestamp
         END, created_at;

SQLfiddle demo.

Note, the '9999-01-01' is somewhat kludgily an arbitrarily selected date far into the future, if anyone knows a PostgreSQL constant for "maxdate", I'd be interested :)

answered Oct 04 '12 at 20:23

Joachim Isaksson

176,943
25
281
294

Postgres has something for you. :) [**`'infinity'` and `'-infinity'`**](http://www.postgresql.org/docs/current/interactive/datatype-datetime.html#AEN5789). Until v8.3 those were only available for the type `timestamp`. Since v8.4 they work for `date`, too. But I am not convinced we need to go to `infinity` (and beyond!) for this case. – Erwin Brandstetter Oct 04 '12 at 22:29
BTW, +1 for providing a working sqlfiddle. I [built upon it](http://sqlfiddle.com/#!12/a45bc/1). I think you missed past events and events with no schedule date. Details in my answer. – Erwin Brandstetter Oct 04 '12 at 22:54

score 2 · Answer 3 · edited May 23 '17 at 11:43

Verbose format

Easier to explain / understand.

WITH x AS (
   SELECT *
        , COALESCE(occurs_at <= (now() + interval '3 days'), FALSE) AS nearby
   FROM   events
   WHERE  occurs_at >= now() - interval '1 hour' -- exclude older events
      OR  occurs_at IS NULL
   )      -- CTE is not strictly necessary, just to make ORDER BY clearer
SELECT *
FROM   x
ORDER  BY
       nearby DESC
     , CASE WHEN nearby THEN occurs_at ELSE created_at END

It's not defined explicitly in the question, but the mentioned "no schedule date" makes me assume that occurs_at can be NULL. So use COALESCE, or FALSE and NULL will be sorted differently.

First, order by nearby calculated in the CTE x.
Next, order by the respective date (occurs_at or created_at).
- occurs_at cannot be NULL for nearby events.
- And I assume created_at is defined NOT NULL to begin with. Else you would have to decide where to put NULL values.

Other answers here suggest to calculate values based on columns before comparing it to a constant, which can lead to very poor performance. Example:

date_diff(d,occurs_at, now) <= 3  -- date_diff is also tSQL, not Postgres

Don't do this if you can avoid it. It forces Postgres to calculate a value for each and every row and makes it impossible to use plain indexes. More infos in this recent closely related answer.

Simple version

Shorter, a bit faster.

SELECT *
FROM   events
WHERE  occurs_at >= now() - interval '1h'
   OR  occurs_at IS NULL
ORDER  BY
       COALESCE( occurs_at <= (now()::date + 3), FALSE) DESC
     , CASE WHEN occurs_at <= (now()::date + 3)
            THEN occurs_at ELSE created_at END;

I took @Joachim's sqlfiddle, simplified the setup, added missing cases (not scheduled, occurred in the past) and ran my queries against it:
-> sqlfiddle

score 1 · Answer 4 · answered Oct 04 '12 at 19:40

I would extend your SELECT clause by a field determining whether or not it's "nearby" (in time, not place)

SELECT
id, user_id, content, occurs_at, created_at,
case when date_diff(d,occurs_at, now) <= 3 then 1 else 0 end as isNearby
...

but I'm not sure of your syntax, this is for MS SQL Server

How to order these SQL results in a certain way?

4 Answers4

Verbose format

Simple version