0

I am trying to do a conditional ORDER BY in SQL but I am getting an error: column "ended" does not exist pointing to the 'case when "ended"' part of the query.

Here is my query:

SELECT (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)
       -EXTRACT(EPOCH FROM now())) AS "datediff"
    , (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)>EXTRACT(EPOCH FROM now())) AS "ended", *
FROM imdown_api_v1_contest
ORDER BY "ended" DESC, case when "ended" = true then "datediff" else "-datediff" END ASC;

I need to grab the contests that haven't ended first and order those by the one ending soonest first. Then I need all the ones that have ended and order those by the ones that ended most recently.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Tyler
  • 2,346
  • 6
  • 33
  • 59
  • One option is to move your query (without the `order by`) into a subquery and then add the `order by` back. That should recognize the `ended` column... – sgeddes Nov 03 '15 at 23:13

2 Answers2

2

column ended is not defined when order by is executed, sql executes query in next order:

  1. from
  2. where
  3. order
  4. select

so, you need either copy your full expression into order by clause or use sub-queries, like:

select * from (
    SELECT (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)-EXTRACT(EPOCH FROM now())) AS "datediff", (EXTRACT(EPOCH FROM SUBMISSION_DEADLINE)>EXTRACT(EPOCH FROM now())) AS "ended", *
    FROM imdown_api_v1_contest
) tmpq
ORDER BY "ended" DESC, case when "ended" = true then "datediff" else "-datediff" END ASC
Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
1

Your query is broken or inefficient in multiple places, not just the illegal reference to a column alias. This might do what you intend:

SELECT EXTRACT(EPOCH FROM now() - submission_deadline) AS datediff
     , now() > submission_deadline AS ended  -- I inverted your expression!
     , *
FROM   imdown_api_v1_contest
ORDER  BY 2  -- positional reference to output column
     , @(EXTRACT(EPOCH FROM now() - submission_deadline))

Typically, contests that "ended" have a submission_deadline in the past, so now() > submission_deadline AS ended, not the other way round.

To fix your primary syntax error I use a positional reference instead of the column alias. Detailed explanation:

Related example:

To achieve your desired order I use the absolute value operator @.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • is there a way to make these work with LIMIT and OFFSET clauses? – Tyler Nov 03 '15 at 23:48
  • @Tyler: I don't see a problem for `LIMIT` and `OFFSET` here ... Maybe start a new question to present your case clearly? Either way, consider the updated query for the question at hand. – Erwin Brandstetter Nov 04 '15 at 00:06