order by help need ASAP - 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 help need ASAP

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-04-28 : 11:25:41

Hi all

I hav a table like as given below and i need to order this table based on the status

OId Time status

1 2009-04-28 03:00:00.000 NR
1 2009-04-28 06:00:00.000 NR
2 2009-04-28 03:00:00.000 DN
2 2009-04-28 03:30:00.000 OD
2 2009-04-28 15:00:00.000 NR
3 2009-04-29 03:00:00.000 DN
3 2009-04-29 06:00:00.000 OD
4 2009-04-30 03:00:00.000 NR
4 2009-04-30 06:00:00.000 NR

first i need to check if anyone of the OID contains status as "OD" then that OID records shud come first. if i have morethan one the rest shud come next.

Like this next i need to check the status = "DN" and so on.

If i dont hav 'OD' records then i shud check for 'DN' and so on.

But the time order shud not be changed.

I need the output like this

OId Time status

2 2009-04-28 03:00:00.000 DN
2 2009-04-28 03:30:00.000 OD
2 2009-04-28 15:00:00.000 NR
3 2009-04-29 03:00:00.000 DN
3 2009-04-29 06:00:00.000 OD
1 2009-04-28 03:00:00.000 NR
1 2009-04-28 06:00:00.000 NR
4 2009-04-30 03:00:00.000 NR
4 2009-04-30 06:00:00.000 NR

because i have status = 'OD' in both oid's 2 & 3. those OID's i shud bring first then the rest.

Kindly help me out from this.

Thanks

Zakeer Sk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 11:49:03

[code]DECLARE @Sample TABLE
(
OId INT,
Time DATETIME,
status CHAR(2)
)

INSERT @Sample
SELECT 1, '2009-04-28 03:00', 'NR' UNION ALL
SELECT 1, '2009-04-28 06:00', 'NR' UNION ALL
SELECT 2, '2009-04-28 03:00', 'DN' UNION ALL
SELECT 2, '2009-04-28 03:30', 'OD' UNION ALL
SELECT 2, '2009-04-28 15:00', 'NR' UNION ALL
SELECT 3, '2009-04-29 03:00', 'DN' UNION ALL
SELECT 3, '2009-04-29 06:00', 'OD' UNION ALL
SELECT 4, '2009-04-30 03:00', 'NR' UNION ALL
SELECT 4, '2009-04-30 06:00', 'NR'

SELECT *
FROM @Sample
ORDER BY MIN(CASE WHEN status IN ('od', 'dn') THEN 0 ELSE 1 END) OVER (PARTITION BY OId),
OId,
Time[/code]

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-28 : 12:02:35

Wow Peso -- yours is a lot more simple than mine :(


DECLARE @foo TABLE (
	[oid] INT
	, [time] DATETIME
	, [Status] CHAR(2)
	)

INSERT @foo
      SELECT 1,	'2009-04-28 03:00:00.000', 'NR'
UNION SELECT 1,	'2009-04-28 06:00:00.000', 'NR'
UNION SELECT 2,	'2009-04-28 03:00:00.000', 'DN'
UNION SELECT 2,	'2009-04-28 03:30:00.000', 'OD'
UNION SELECT 2,	'2009-04-28 15:00:00.000', 'NR'
UNION SELECT 3,	'2009-04-29 03:00:00.000', 'DN'
UNION SELECT 3,	'2009-04-29 06:00:00.000', 'OD'
UNION SELECT 4,	'2009-04-30 03:00:00.000', 'NR'
UNION SELECT 4,	'2009-04-30 06:00:00.000', 'NR'

SELECT * FROM @foo ORDER BY [oid] ASC

SELECT
	r.[oid]
	, r.[time]
	, r.[status]
FROM
	(
	SELECT
		ROW_NUMBER() OVER(PARTITION BY f.[oid] ORDER BY CASE [status] WHEN 'OD' THEN 2 WHEN 'DN' THEN 1 WHEN 'NR' THEN 3 END) AS [pos]
		, ISNULL(b.[ODexists], 0) AS [odExists]
		, f.[oid] AS [oid]
		, f.[time] AS [time]
		, f.[status] AS [status]
	FROM
		@foo f
		LEFT JOIN (SELECT [oid] AS [oid], 1 AS [odExists] FROM @foo WHERE [status] = 'OD') b ON b.[oid] = f.[oid]
	)
	r
ORDER BY
	[odExists] DESC
	, [oid] ASC
	, [pos] ASC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-28 : 12:07:54

quote:

MIN(CASE WHEN status IN ('od', 'dn') THEN 0 ELSE 1 END) OVER (PARTITION BY OId),

nice.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 12:11:24

Thank you.

E 12°55'05.63"
N 56°04'39.26"

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-04-28 : 23:17:17

Hi friends

thanks for ur prompt reply.

Like this i have around 10 status then how can i check.....

Thanks

Zakeer Sk

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 23:31:53

quote:
Originally posted by shaik.zakeer

Hi friends

thanks for ur prompt reply.

Like this i have around 10 status then how can i check.....

Thanks

Zakeer Sk

specify this in the CASE statement


CASE WHEN status IN ('OD', 'DN') THEN 0 ELSE 1 END

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-29 : 04:45:27

Hi All.

1) I'm very impressed with Peso's solution. I've never used a PARTITION except during ROW_NUMBER() so was a little blown away with it. However, -- how do you specify the sort order on the status column?

2) shaik.zakeer -- you can set up any order for the status you like using my method -- just add entries to the CASE statement in the ROW_NUMBER() Call. My method will be a bit slower than Peso's I imagine but it should still be reasonably fast and light weight.

Peso will be along at some point to show us how it should be done though!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-29 : 04:51:57

Blurgh -- I'm dense this morning!

Here's Peso's code with arbitrary ordering!


DECLARE	@Sample TABLE
	(
		OId INT,
		Time DATETIME,
		status CHAR(2)
	)

INSERT	@Sample
SELECT	1, '2009-04-28 03:00', 'NR' UNION ALL
SELECT	1, '2009-04-28 06:00', 'NR' UNION ALL
SELECT	2, '2009-04-28 03:00', 'DN' UNION ALL
SELECT	2, '2009-04-28 03:30', 'OD' UNION ALL
SELECT	2, '2009-04-28 15:00', 'NR' UNION ALL
SELECT	3, '2009-04-29 03:00', 'DN' UNION ALL
SELECT	3, '2009-04-29 06:00', 'OD' UNION ALL
SELECT	4, '2009-04-30 03:00', 'NR' UNION ALL
SELECT	4, '2009-04-30 06:00', 'NR'

SELECT		*
FROM		@Sample
ORDER BY
	MIN(CASE WHEN status IN ('od', 'dn') THEN 0 ELSE 1 END) OVER (PARTITION BY OId),
	OId,
	CASE [status]
		WHEN 'DN' THEN 1
		WHEN 'OD' THEN 2
		WHEN 'NR' THEN 3
	END ASC,
	TIME

It's easy to add more status's -- just add them to the


MIN(CASE WHEN...

And then you can add more entries to the[code]
CASE [status]
[code]
block to guarantee the order they appear in.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 06:05:55

[code]SELECT *
FROM @Sample
ORDER BY MIN(CASE status
WHEN 'dn' THEN 1
WHEN 'od' THEN 2
WHEN 'nr' THEN 3
ELSE 4
END) OVER (PARTITION BY OId),
OId,
TIME[/code]

E 12°55'05.63"
N 56°04'39.26"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-29 : 08:35:16

Yes, Peter does OK for being an Entry Level DBA™

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-04 : 08:08:22

Thank you!
I'll let you know after the 1st of July.

E 12°55'05.63"
N 56°04'39.26"

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-05-13 : 23:18:09

Hi

Peso's solution helped me alot...

its awesome... thanks

Thanks

Zakeer Sk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources