Re: Conditional 'Order By' statement

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
.