[RESOLVED] Sort X Records One Way and X Records Another Way

[partypete]

Hello,

I've got a table full of promotions for a business. Each promotion has a start_date and an end_date field. The problem is that when I try to display these records some need to be sorted by end_date and other records by the start_date.

My Table "promos":

promo_type          start_date         end_date
20% Off!            2006-11-03         2006-11-12 (Active)
Free Trip!          2006-11-18         2006-11-19 (Approaching)
No Taxes!           2006-11-12         2006-11-30 (Active)
$100 Rebate         2006-11-16         2006-11-25 (Approaching)
Free TV!            2006-11-01         2006-11-15 (Active)

Basically I want the records listed so that promotions that are active (where todays_date is >= start_date AND todays_date <= end_date ) are listed first and sorted by end_date, and records that are not yet active ( where todays_date < start_date ) are listed second and sorted by start_date.

So if todays_date was 2006-11-12, the records should display as:

promo_type          start_date         end_date
20% Off!            2006-11-03         2006-11-12 (Active)
Free TV!            2006-11-01         2006-11-15 (Active)
No Taxes!           2006-11-12         2006-11-30 (Active)
$100 Rebate         2006-11-16         2006-11-25 (Approaching)
Free Trip!          2006-11-18         2006-11-19 (Approaching)

Note that the query must detect and sort multiple points...
- Is the promo active (todays_date between start_date and end_date)
- or is promo approaching (todays_date less than start_date)
- Sort active promos first by end_date ASC
- Sort approaching promos second by start_date ASC

I get all the required records with the following query, but don't know how to get the multiple sorts needed:

SELECT promo_type, start_date, end_date FROM promos WHERE DATE_ADD(CURDATE(), INTERVAL 21 DAY) >= start_date AND end_date >= CURDATE()

This could easily be done with two queries (one selecting and sorting only active promos and another selecting and sorting approaching promos), but was wondering if it could be done with a single more efficient one?

Thanks as always!
Peter

[NogDog]

Maybe something like this?

SELECT promo_type, start_date, end_date, 
IF(CURDATE() < start_date, 0, 1) AS active_status,
IF(active_status = 1, end_date, start_date) AS sort_date
FROM promos WHERE DATE_ADD(CURDATE(), INTERVAL 21 DAY) >= start_date 
AND end_date >= CURDATE() ORDER BY active_status DESC, sort_date ASC

[partypete]

Thanks NogDog. I've never seen IF in a query before so I'm off to the MySQL manual to learn about it and then I will give it a shot.

[partypete]

Hey NogDog,

I received an error when trying the query as written:

Error Code : 1054
Unknown column 'active_status' in 'field list'

Is there a way to refer to a temporary field as you attempted to do? Would come in handy if that can be done.

To get it working without errors, I removed "active_status = 1" and replaced it with another condition. The working query I have now is:

SELECT promo_type, start_date, end_date, 
IF(CURDATE() < start_date, 1, 2) AS active_status,
IF(CURDATE() < start_date, start_date, end_date) AS sort_date
FROM promos WHERE DATE_ADD(CURDATE(), INTERVAL 21 DAY) >= start_date 
AND end_date >= CURDATE() ORDER BY active_status DESC, sort_date ASC

Thank you very much for your help!
Peter