CODEWITHSUNDEEP
phpsqlpostgresql
Alec
Alec

Reputation: 979

How to sort with priority and dates?

I have the following PostgreSQL query.

SELECT id FROM table WHERE user_id=$user_id ORDER BY
    CASE
      WHEN (date_completed_utc='0000-00-00 00:00:00') THEN 0 ELSE 1 END ASC

Here is an example of what's in the table:

enter image description here

date_requested_utc is always a past date.

date_completed_utc is defaulted to '0000-00-00 00:00:00', but is later changed to a date newer than date_requested_utc.

What I'm trying to accomplish is this:

If date_completed_utc is the default, then all these rows will be above all else, but these rows are sorted by date_requested_utc descending.

But if date_completed_utc is not the default, then put these rows at the bottom and order these rows by date_completed_utc descending.

The resulting order by ID should be as follows:

26, 20, 18, 17, 15, 16, 27, 28

I've tried all these different CASE statements, but can't seem to get it right.

Upvotes: 0

Views: 345

Answers (2)

halfer
halfer

Reputation: 20469

(Posted answer on behalf of the question author).

I changed my query to the following and it's now working fine.

(SELECT id FROM table WHERE user_id=$user_id AND date_completed_utc='0000-00-00 00:00:00' ORDER BY date_requested_utc DESC)
UNION ALL
(SELECT id FROM table WHERE user_id=$user_id AND date_completed_utc!='0000-00-00 00:00:00' ORDER BY date_completed_utc DESC)

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

You can try to use double orders.

SELECT id 
FROM table 
WHERE user_id=$user_id 
ORDER BY
    CASE WHEN (date_completed_utc='0000-00-00 00:00:00') THEN 0 ELSE 1 END,
    date_requested_utc DESC

EDIT

I saw your edit question.

You don't have to use UNION ALL combine two query,you can query one with CASE WHEN expression directly.

Order by the number

  1. 1/(EXTRACT(epoch from age(now(),date_requested_utc)) / 86400 date_requested_utc closer the time will get larger the number.
  2. 1/(EXTRACT(epoch from age(now(),date_completed_utc::timestamp)) / 86400)::numeric date_completed_utc closer the time will get larger the number.

you can try this.

SELECT id
SELECT id FROM table 
WHERE user_id=$user_id 
ORDER BY
    (
      CASE WHEN (date_completed_utc='0000-00-00 00:00:00') 
        THEN (EXTRACT(epoch from age(now(),'1970-01-01')) / 86400) + 1/(EXTRACT(epoch from age(now(),date_requested_utc)) / 86400)::numeric
        ELSE  1/(EXTRACT(epoch from age(now(),date_completed_utc::timestamp)) / 86400)::numeric
      END
    )::numeric 
    DESC

sqlfiddle:http://sqlfiddle.com/#!17/f5f88a/28

Upvotes: 2

Related Questions