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. >
