Ravindra, Sorry, made a mistake in my last post. Meant to write:
order by case when (date_field_3 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_1 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; -----Original Message----- From: Nathan Sullivan Sent: Thursday, July 02, 2009 1:07 PM To: 'Ravindra Harige'; mysql@lists.mysql.com Subject: RE: Adhoc sorting requirement 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.har...@gmail.com] Sent: Thursday, July 02, 2009 12:18 PM To: mysql@lists.mysql.com 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
