Ordering problem - 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 2000 Forums

SQL Server Development (2000)

Ordering problem

Author

Topic

ChetShah
Starting Member

37 Posts

Posted - 2007-12-05 : 10:40:45

Hi SQL Gurus,

How do you re-order the following results:

ClaimReference StatusFrom StatusTo StartDate EndDate
*DCOU00004 Outsource - Off Site Rejected - Approved 2006-10-03 11:14:20.000 NULL LCHUNG
*DCOU00004 Rejected - Approved Rejected - Approved 2006-10-03 11:14:20.000 2006-10-03 11:14:20.000 LCHUNG

Outcome:
I would like it so that i can re-order the results so that the EndDate which contains NULL value is the last record. At present it shows up as first record.

Thanks
Chet

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:50:59

[code]-- Prepare sample data
DECLARE @Sample TABLE (ClaimReference VARCHAR(200), Status VARCHAR(200), StartDate DATETIME, EndDate DATETIME)

INSERT @Sample
SELECT '*DCOU00004', 'Outsource - Off Site Rejected - Approved', '2006-10-03 11:14:20.000', NULL UNION ALL
SELECT '*DCOU00004', 'Rejected - Approved Rejected - Approved', '2006-10-03 11:14:20.000', '2006-10-03 11:14:20.000'

-- Show the expected output
SELECT *
FROM @Sample
ORDER BY CASE
WHEN EndDate IS NULL THEN 1
ELSE 0
END,
EndDate[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 02:13:41


SELECT		*
FROM		@Sample
ORDER BY	COALESCE(EndDate,'99991231')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources