CODEWITHSUNDEEP
phpmysqlsqldatabase
Adam
Adam

Reputation: 1

ordering by grouped dates in database

hi got a similar problem to this question

Tricky "grouped" ordering in SQL

but cant seem to make it work with dates

my table sort of

Car_ID   Car_Brand   Car_Model   Car_launch_date
   1     Ford        Fiesta      12-12-2011
   2     Ford        Mustang     09-04-2008
   3     Ford        Focus       10-02-2012
   4     Honda       Civic       11-05-2012
   6     Honda       Jazz        02-05-2011
   7     Toyota      Prius       11-10-2011

i want my results ordered by future dates closest to now, then followed by past dates closest to now (and grouped by brand)

I want my output to be this:

Car_ID   Car_Brand   Car_Model   Car_launch_date
   7     Toyota      Prius       11-10-2011
   1     Ford        Fiesta      12-12-2011
   3     Ford        Focus       10-02-2012
   2     Ford        Mustang     09-04-2008
   4     Honda       Civic       11-05-2012
   6     Honda       Jazz        02-05-2011

Upvotes: 0

Views: 61

Answers (2)

Dave
Dave

Reputation: 11899

Are your dates in the database as DATE objects or strings? Because if you try to order them as strings, that won't work!

If the dates are DATEs, try:

SELECT * from my_table
ORDER BY Car_Brand DESC, 
         Car_launch_date < NOW(), 
         ABS(DATEDIFF(Car_launch_date, NOW()))

If they're strings, try:

SELECT * from my_table
ORDER BY Car_Brand DESC, 
         DATE(Car_launch_date) < NOW(), 
         ABS(DATEDIFF(DATE(Car_launch_date), NOW()))

When you specify more than one column in an order by, it orders by the first column, and then by the next if the values in the first column are the same.

This orders by - Car Brand, then - Whether the date is in the past. Falses are ordered first. (So future first), then - Absolute difference in dates (smallest number first)

Note: I put Car_Brand DESC (reverse alpha order) because that's how you had it above.

Thanks to the OP for spotting that you wanted a more complicated order. But I'd argue this way is better : )

Upvotes: 1

Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

Using previous post as a supplement, the better answer would be:

GROUP BY Car_Brand 
ORDER BY 
CASE WHEN Car_launch_date >= NOW() THEN 0 ELSE 1 END, 
CASE WHEN Car_launch_date >= NOW() THEN DATEDIFF(Car_launch_date, NOW()) ELSE DATEDIFF(NOW(), Car_launch_date) END

The query uses conditionnal statements to determine first if it's a future or past date. If it's future, it outputs 0 and past it outputs 1 so the future dates will sort first and past dates sort second. Next another case statement to detect how to compute the difference of days between now and the launch date.

Upvotes: 0

Related Questions