Order posts by "rep" but only for "one" day limit

1
votes

i'm trying to get posts ordering by rep, but if post.date older than one day then it must be ordered in normal order (means id field order).

here is posts table (date is unix timestamp);

id | date       | rep | title
------------------------------
10 | today      | 0   | lorem
9  | today      | 1   | ipsum
8  | yesterday  | 2   | dolor
7  | 2 days ago | 2   | sit
6  | 3 days ago | 10  | amet

expected result (is it impossible or nonsense trying to get a result like that, or am i confused);

id | title
----------
9  | ipsum
10 | lorem
8  | dolor
7  | sit
6  | amet

here is what i tested that returns too old posts first;

ORDER BY CASE WHEN
  p.date + unix_timestamp(from_unixtime(p.date) + interval 1 day)
    > unix_timestamp(now()) 
  THEN p.rep ELSE p.id
END DESC
// or
...
END DESC, p.id DESC
2
mysqlsorting

2 Answers

1
votes

Try:

ORDER BY 
  CASE WHEN
    p.date >= unix_timestamp(now() - interval 1 day)
      -- this part edited to worked vers
      -- THEN -p.rep ELSE NULL 
      THEN +p.rep ELSE NULL 
  END 
DESC, p.id DESC;

If I understand correctly, this should order by rep for anything more recent than 1 day old. The -p.rep and DESC should work to put NULLs last in this first layer of ordering. For all the rows where the date is older than one day, the first layer of ordering would have been NULL and would appear last, and you can subsequently order by p.id in whatever order you wish.

0
votes

what about doing 2 different queries then UNION them ?

(SELECT *
FROM POST
WHERE [YOUR CONDITION ON DATES > YESTERDAY]
ORDER BY rep DESC, id DESC)
UNION
(SELECT *
FROM POST
WHERE [YOUR CONDITION ON DATES < YESTERDAY]
ORDER BY id DESC);