List:General Discussion« Previous MessageNext Message »
From:Ravindra Harige Date:July 2 2009 6:31pm
Subject:Re: Adhoc sorting requirement
View as plain text  
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 <nsullivan@stripped>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@stripped
> 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.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.
>

Thread
Adhoc sorting requirementRavindra Harige2 Jul
  • RE: Adhoc sorting requirementNathan Sullivan2 Jul
  • RE: Adhoc sorting requirementNathan Sullivan2 Jul
    • Re: Adhoc sorting requirementRavindra Harige2 Jul
      • Re: Adhoc sorting requirementRavindra Harige5 Jul