ORDER BY CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ORDER BY CASE

Author

Topic

robson
Starting Member

22 Posts

Posted - 2008-07-10 : 09:29:27

Hi Everyone,

I have the following stored procedure

ALTER PROCEDURE <SP_NAME>
@patientID int,
@admissionID int,
@row_number int
AS

SELECT TOP 10 *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY completed_date, recordCreated DESC) AS RowNumber,
doctorID, locum_doctorUsername, completed_date, allocated, add_sheet, patient_recordID,
recordCreated, location
FROM patient_record
WHERE patientID = @patientID AND admissionID = @admissionID) _myResults
WHERE RowNumber > @row_number

I would like to order on completed_date if it is empty i.e only if it is > completed_date = '' OR completed_date IS NULL, and also recordCreated DESC.
Otherwise if completed_date is not empty I only need to ORDER ON
recordCreated DESC.

I then changed the above SP to:

ALTER PROCEDURE <SP_NAME>
@patientID int,
@admissionID int,
@row_number int
AS

SELECT TOP 10 *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN completed_date = '' OR completed_date IS NULL THEN completed_date, recordCreated DESC ELSE recordCreated DESC END) AS RowNumber,
doctorID, locum_doctorUsername, completed_date, allocated, add_sheet, patient_recordID,
recordCreated, location
FROM patient_record
WHERE patientID = @patientID AND admissionID = @admissionID) _myResults
WHERE RowNumber > @row_number

This does not work, please can you help?

MakeYourDaddyProud

184 Posts

Posted - 2008-07-10 : 10:08:22

What you are asking for is two different sort mechanisms in 1 sql.

Either break it up with a union, or use a Table Value Function using a RETURNSTABLE construct, thus...

CREATE function selectstatement(@iTOPNVALUE)
returns @returntable table
-- Insert Target in this function
(
-- Columns returned by the function
col1 int primary key identity (1,1),
col2 definition,
etc etc
)
as
begin
INSERT INTO @returntable (col1, col2, etc etc)
SELECT <yourselectstatement>
ORDER BY order1
WHERE col1 IS null
INSERT INTO @returntable (col1, col2, etc etc)
SELECT <yourselectstatement>
ORDER BY order2
WHERE col1 IS NOT null
Go
end

>>>>> THE Whammy Bar String Trasher <<<<<

robson
Starting Member

22 Posts

Posted - 2008-07-10 : 10:17:56

Hi danny2sweet,

No idea of what you are trying to tell me.

MakeYourDaddyProud

184 Posts

Posted - 2008-07-10 : 10:27:00

Try this?????

SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY _myResults.LIST_ORDER), _myResults.*
FROM
(SELECT doctorID, locum_doctorUsername, completed_date, allocated, add_sheet, patient_recordID, recordCreated, location,
-- Cast a numeric sort
CASE WHEN completed_date = '' OR completed_date IS NULL THEN 1 ELSE 2 END AS LIST_ORDER
FROM patient_record
WHERE patientID = @patientID AND admissionID = @admissionID
AND completed_date IS NULL) as _myResults

>>>>> THE Whammy Bar String Trasher <<<<<

robson
Starting Member

22 Posts

Posted - 2008-07-10 : 10:57:17

Thanks danny2sweet

But this does not return any results.

MakeYourDaddyProud

184 Posts

Posted - 2008-07-10 : 11:05:34

Try this...

SELECT TOP 10 *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY _myResults.LIST_ORDER) as RowN, _myResults.*
FROM
(SELECT doctorID, locum_doctorUsername, completed_date, allocated, add_sheet, patient_recordID, recordCreated, location,
-- Cast a numeric sort
CASE WHEN completed_date = '' OR completed_date IS NULL THEN 1 ELSE 2 END AS LIST_ORDER
FROM patient_record
WHERE patientID = @patientID AND admissionID = @admissionID) as _myResults) as D2
WHERE D2.RowN > @row_number

>>>>> THE Whammy Bar String Trasher <<<<<

robson
Starting Member

22 Posts

Posted - 2008-07-10 : 11:20:34

Many Thanks danny2sweet, it works a treat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources