Hi Nathan.. It worked..! :) thanks again :)
Regards, Ravi On Fri, Jul 3, 2009 at 12:01 AM, Ravindra Harige <ravindra.har...@gmail.com>wrote: > Thanks Nathan for the quick reply. will try this and report back.. :) > Regards, > Ravi > > > On Thu, Jul 2, 2009 at 11:51 PM, Nathan Sullivan <nsulli...@cappex.com>wrote: > >> 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. >> > >
