Conditional 'Order By' statement

I have a table that stores three columns of data, namely
1. Order_ID
2. Exam_Start_Date
3. Order_Received_Date
I want to order the records as follows:
If the Exam_Start_Date is within the next 10 days or past, then order by the
exam_start_date. Otherwise order by the order_Received_Date. Therefore the
result set should display records where the exam_start_date is within next 10
days first, then display all other records.
I have tried the following SQL but it doesnt appear to work (i.e. Order of
the records is not exam_start_date (if within next 10 days), otherwise
Order_Received_Date.
select order_id, order_received_date, exam_start_date
from orders
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
Order_Received_Date END
Any suggestions greatly appreciated!
Wes.
On Sat, 7 Jan 2006 18:51:02 -0800, Wez wrote:

>I have a table that stores three columns of data, namely
>1. Order_ID
>2. Exam_Start_Date
>3. Order_Received_Date
>I want to order the records as follows:
>If the Exam_Start_Date is within the next 10 days or past, then order by the
>exam_start_date. Otherwise order by the order_Received_Date. Therefore the
>result set should display records where the exam_start_date is within next 10
>days first, then display all other records.
>I have tried the following SQL but it doesnt appear to work (i.e. Order of
>the records is not exam_start_date (if within next 10 days), otherwise
>Order_Received_Date.
>select order_id, order_received_date, exam_start_date
>from orders
>ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
>Order_Received_Date END
>Any suggestions greatly appreciated!
Hi Wes,
Try if this works better:
ORDER BY CASE WHEN Exam_Start_date < '20050118'
THEN Exam_Start_Date
ELSE '20050118'
END DESC,
Order_Received_Date DESC
Or, a more generic version:
ORDER BY CASE WHEN Exam_Start_date < DATEADD(day,-10, CURRENT_TIMESTAMP)
THEN Exam_Start_Date
ELSE DATEADD(day,-10, CURRENT_TIMESTAMP)
END DESC,
Order_Received_Date DESC
Hugo Kornelis, SQL Server MVP