RE: Adhoc sorting requirement

[Nathan Sullivan]

Ravindra,

Maybe something like this?

order by
case
when (date_field_1 between date(current_timestamp) and date(date_add(current_timestamp,
interval 2 day))) then 1
when (date_field_2 between date(current_timestamp) and date(date_add(current_timestamp,
interval 2 day))) then 2
when (date_field_3 between date(current_timestamp) and date(date_add(current_timestamp,
interval 2 day))) then 3
else 4
end, date_field_3, date_field_2, date_field_1;


Hope it helps..

Regards,
Nathan

-----Original Message-----
From: Ravindra Harige [mailto:ravindra.harige@stripped] 
Sent: Thursday, July 02, 2009 12:18 PM
To: mysql@stripped
Subject: Adhoc sorting requirement

Hi,

I have a very ad hoc sorting requirement and would like to know how this can
be achieved.
The requirement is as follows:
I have a table (of say 100 records) with 3 DATE fields:
date_field_1 , date_field_2 , date_field_3

I have a date range, for eg. from (today) to (today+2) and
Each one of the date_fields has a priority
date_field_3 : First priority
date_field_2 : Second priority
date_field_1: Third priority

Now, I want the query result to be sorted and shown in the following order-
records whose
1.(date_field_3) is in date range [from (today) to (today+2) ]

2.(date_field_2) is in date range [from (today) to (today+2) ]

3.(date_field_1) is in date range [from (today) to (today+2) ]

and then rest of the records (ie not in range) should be ordered according
to

date_field_3,
date_field_2,
and date_field_1

Any help on this will be appreciated :)

Thanks.