Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sorting data according to current date

Tags:

sql

mysql

I have a table of posts like this

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

What I want achieve is to order by published_at. Normally I would do:

SELECT * FROM posts ORDER BY published_at;

But my requirement here is that the query should fetch the results from current date on top and then the previous ones and after that fetch those from future.

Current my 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 |
+-------------------------------+----+---------------------+  

Whereas my desired outcome would be:

+-------------------------------+----+---------------------+
| 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 |
+-------------------------------+----+---------------------+
like image 688
usmanali Avatar asked Aug 25 '15 10:08

usmanali


People also ask

How do I sort by date in SQL?

Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).

How do you sort descending dates?

When arranging them in descending order, you'd write them out from Z to A (so, backwards). Think of this one from end to beginning. The same goes for dates when you're putting things in chronological order – they're arranged from the latest and most recent one to the oldest one.

How do I sort a date by month in SQL?

To order by month, create a date with this month. To do this, use the STR_TO_DATE() function. If you have a date stored as a string in the ' Year Month Day ' format, you can cast it to a date using STR_TO_DATE(date_string, '%Y %M %d') . The CONCAT() function combines all the arguments into one string.


1 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. Its working fine with stimulated data. You can change desc to asc according to your sorting requirement for past and future dates

like image 163
Excel Avatar answered Oct 20 '22 18:10

Excel