Geek Answers Handbook

Sort data according to current date

I have a table postslike this

+--------------------+--------------+
| Field              | Type         |
+--------------------+--------------+
| id                 | int(11)      |
| title              | varchar(255) |
| body               | text         | 
| published_at       | datetime     |
+--------------------+--------------+

I want to reach orderon published_at. Normally I would do:

SELECT * FROM posts ORDER BY published_at;

But my requirement here is that the query should retrieve the results from the current date from above, and then to the previous ones, and then retrieve them from the future.

My current results are as follows:

+-------------------------------+----+---------------------+
| title                         | id | published_at        |
+----------------------------------------------------------|
| Hello world                   |  1 | 2015-01-06 12:21:16 |
| 20+ Tools For RoR Development |  2 | 2015-08-25 12:21:23 |
| Angular JS tutorial           |  3 | 2015-09-31 10:51:55 |
| Visual search                 |  4 | 2015-03-12 12:27:26 |
| Ruby on Rails best practices  |  5 | 2015-01-21 00:00:00 |
+-------------------------------+----+---------------------+

While my desired result would be as follows:

+-------------------------------+----+---------------------+
| title                         | id | published_at        |
+----------------------------------------------------------|
| 20+ Tools For RoR Development |  2 | 2015-08-25 12:21:23 |
| Hello world                   |  1 | 2015-01-06 12:21:16 |
| Ruby on Rails best practices  |  5 | 2015-01-21 00:00:00 |
| Visual search                 |  4 | 2015-03-12 12:27:26 |
| Angular JS tutorial           |  3 | 2015-09-31 10:51:55 |
+-------------------------------+----+---------------------+
+4
source share
5 answers

Solution without using UNION / CASE

SELECT * FROM posts
ORDER BY
    DATE(published_at)=DATE(NOW()) DESC,
    DATE(published_at)<DATE(NOW()) DESC,
    DATE(published_at)>DATE(NOW()) ASC`

check if this works. It works great with stimulated data. You can change desc to asc according to your sorting requirement for past and future dates.

+5
source

UNION (ALL):

select
 id, title, published_at,
 case
   when date(published_at) = curdate() then '1-now'
   when date(published_at) < curdate() then '2-past'
   else '3-future'
   end as order_group
from t
order by order_group asc, published_at asc;

http://sqlfiddle.com/#!9/59c48/2

, , UNION ALL . UNION , , .

+4

Try:

SELECT * FROM posts WHERE published_at = CONVERT(DATE, GETDATE())
UNION
SELECT * FROM posts WHERE published_at != CONVERT(DATE, GETDATE()) ORDER BY published_at;
+1
source

Using a different approach without the need for Unions.

SELECT *
FROM(
    SELECT 1 AS Rank, title, id, published_at FROM posts WHERE DATE(published_at) = CURDATE()
    UNION ALL
    SELECT 2 AS Rank, title, id, published_at FROM posts WHERE DATE(published_at) < CURDATE()  
    UNION ALL
    SELECT 3 AS Rank, title, id, published_at FROM posts WHERE DATE(published_at) > CURDATE()
) a
ORDER BY rank, published_at

OUTPUT:

Rank    title                           id  published_at
1       20+ Tools For RoR Development   2   August, 25 2015 12:21:23
2       Hello world                     1   January, 06 2015 12:21:16
2       Ruby on Rails best practices    5   January, 21 2015 00:00:00
2       Visual search                   4   March, 12 2015 12:27:26
3       Angular JS tutorial             3   October, 01 2015 10:51:55

SQL Fiddle: http://sqlfiddle.com/#!9/f6c1b/9/0

+1
source 
SELECT * 
FROM posts 
WHERE date(published_at) = CURDATE()

UNION ALL

SELECT * 
FROM posts 
WHERE date(published_at) != CURDATE() 
ORDER BY published_at;

CURDATE()will give today date without time
date(published_at)will crop timefrompublished_at

0
source

More articles:


All Articles