Clever Geek Handbook

Order messages "rep", but only for "one" daily limit

I'm trying to get the ordering of posts using rep

, but if post.date

older than one day then it needs to be ordered in normal order (means ordering order id

).

here 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 (it is impossible or pointless to try to get such a result, or I am confused);

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

here is what i tested which returns too old messages 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
+1


source to share


2 answers


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 book recommend for something more recent than a 1 day. -p.rep and DESC should work to put NULLs in this first ordering level. For all rows where the date is older than one day, the first ordering level would be NULL and appear last, and you can subsequently order p.id in any order.

+1


source


how about doing two different queries and then UNION?



(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);
0


source






More articles:


All Articles