Adhoc sorting requirement

--000e0cd29170e359fd046dbc3aff
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.

--000e0cd29170e359fd046dbc3aff--

RE: Adhoc sorting requirement

Ravindra,

Maybe something like this?

order by
case
when (date_field_1 between date(current_timestamp) and date(date_add(curren=
t_timestamp, interval 2 day))) then 1
when (date_field_2 between date(current_timestamp) and date(date_add(curren=
t_timestamp, interval 2 day))) then 2
when (date_field_3 between date(current_timestamp) and date(date_add(curren=
t_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@gmail.com]=20
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 ca=
n
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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Adhoc sorting requirement

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(curren=
t_timestamp, interval 2 day))) then 1
when (date_field_2 between date(current_timestamp) and date(date_add(curren=
t_timestamp, interval 2 day))) then 2
when (date_field_1 between date(current_timestamp) and date(date_add(curren=
t_timestamp, interval 2 day))) then 3
else 4
end, date_field_3, date_field_2, date_field_1;

-----Original Message-----
From: Nathan Sullivan=20
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(curren=
t_timestamp, interval 2 day))) then 1
when (date_field_2 between date(current_timestamp) and date(date_add(curren=
t_timestamp, interval 2 day))) then 2
when (date_field_3 between date(current_timestamp) and date(date_add(curren=
t_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@gmail.com]=20
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 ca=
n
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Adhoc sorting requirement

--000e0cd28fd2545022046dbd3f87
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--000e0cd28fd2545022046dbd3f87--

Re: Adhoc sorting requirement

--000e0cd214925f704a046df67b5d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi Nathan..

It worked..! :) thanks again :)

Regards,
Ravi

On Fri, Jul 3, 2009 at 12:01 AM, Ravindra Harige
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 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.harige@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.
>>
>
>

--000e0cd214925f704a046df67b5d--