Menu
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 |
+-------------------------------+----+---------------------+
688
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.).
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.
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.
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
163
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
