Alternate order - 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 2008 Forums

Transact-SQL (2008)

Alternate order

Author

Topic

rico1931
Starting Member

37 Posts

Posted - 2009-05-22 : 10:29:02

Hello All,

I have a query that I want the results to alternate order.

ID KEY REGION
975 100 LEFT
1083 100 LEFT
1084 100 LEFT
999 100 LEFT
1010 100 LEFT
1011 100 LEFT
1000 100 LEFT
1002 100 LEFT
1016 100 MIDDLE
36 100 MIDDLE
3 100 MIDDLE
1034 100 MIDDLE

I want the top to look like
for the 3rd column. Is there an easy way to do this? or a keyword i'm missing?

975 100 LEFT
1083 100 MIDDLE
1084 100 LEFT
999 100 MIDDLE
1010 100 LEFT
1011 100 MIDDLE
1000 100 LEFT
1002 100 MIDDLE
1016 100 MIDDLE
36 100 LEFT
3 100 MIDDLE
1034 100 LEFT

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 10:41:44

how do you determine the ordering of the records ? based on which column ?

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

rico1931
Starting Member

37 Posts

Posted - 2009-05-22 : 10:54:38

ok currently I have it by SEQ_NUM
975 100 LEFT SubheadMini-Blue USEN 10
1083 100 LEFT SubheadMini-Blue USEN 20
1084 100 LEFT Pgph-Mini-Black USEN 26
999 100 LEFT SubheadMini-Blue USEN 30
1010 100 LEFT Default USEN 35
1011 100 LEFT Pgph-Mini-Black USEN 37
1000 100 LEFT Default USEN 40
1002 100 LEFT Pgph-Mini-Black USEN 50
1016 100 MIDDLE Default USEN 60
36 100 MIDDLE Header-Blue USEN 65
3 100 MIDDLE Pgph-1.25 USEN 70
1034 100 MIDDLE Default USEN 75
1012 100 MIDDLE SubHeader-Red-L USEN 80
1014 100 MIDDLE Paragraph USEN 85
2 100 MIDDLE SubHeader USEN 90
985 100 RIGHT Pgph-Mini-Black USEN 95

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:02:06

so you want to alternate the region between LEFT & MIDDLE ? How about RIGHT ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:11:42

[code]
DECLARE @data TABLE
(
[ID] int,
[KEY] int,
REGION varchar(10),
OTHERS varchar(30),
SEQ_NUM int
)
INSERT INTO @data ([ID], [KEY], REGION, OTHERS, SEQ_NUM)
SELECT 975, 100, 'LEFT', 'SubheadMini-Blue', 10 UNION ALL
SELECT 1083, 100, 'LEFT', 'SubheadMini-Blue', 20 UNION ALL
SELECT 1084, 100, 'LEFT', 'Pgph-Mini-Black', 26 UNION ALL
SELECT 999, 100, 'LEFT', 'SubheadMini-Blue', 30 UNION ALL
SELECT 1010, 100, 'LEFT', 'DEFAULT', 35 UNION ALL
SELECT 1011, 100, 'LEFT', 'Pgph-Mini-Black', 37 UNION ALL
SELECT 1000, 100, 'LEFT', 'DEFAULT', 40 UNION ALL
SELECT 1002, 100, 'LEFT', 'Pgph-Mini-Black', 50 UNION ALL
SELECT 1016, 100, 'MIDDLE', 'DEFAULT', 60 UNION ALL
SELECT 36, 100, 'MIDDLE', 'Header-Blue', 65 UNION ALL
SELECT 3, 100, 'MIDDLE', 'Pgph-1.25', 70 UNION ALL
SELECT 1034, 100, 'MIDDLE', 'DEFAULT', 75 UNION ALL
SELECT 1012, 100, 'MIDDLE', 'SubHeader-Red-L', 80 UNION ALL
SELECT 1014, 100, 'MIDDLE', 'Paragraph', 85 UNION ALL
SELECT 2, 100, 'MIDDLE', 'SubHeader', 90 UNION ALL
SELECT 985, 100, 'RIGHT', 'Pgph-Mini-Black', 95

;WITH data ([ID], [KEY], [REGION], [OTHERS], [SEQ_NUM], [ROW_NO])
AS
(
SELECT [ID], [KEY], [REGION], [OTHERS], [SEQ_NUM],
[ROW_NO] = ROW_NUMBER() OVER (ORDER BY SEQ_NUM)
FROM @data
)
SELECT *, CASE WHEN ROW_NO % 2 = 1 THEN 'LEFT' ELSE 'MIDDLE' END
FROM data

/*
ID KEY REGION OTHERS SEQ_NUM ROW_NO
----------- ----------- ---------- ------------------------------ ----------- -------------------- ------
975 100 LEFT SubheadMini-Blue 10 1 LEFT
1083 100 LEFT SubheadMini-Blue 20 2 MIDDLE
1084 100 LEFT Pgph-Mini-Black 26 3 LEFT
999 100 LEFT SubheadMini-Blue 30 4 MIDDLE
1010 100 LEFT DEFAULT 35 5 LEFT
1011 100 LEFT Pgph-Mini-Black 37 6 MIDDLE
1000 100 LEFT DEFAULT 40 7 LEFT
1002 100 LEFT Pgph-Mini-Black 50 8 MIDDLE
1016 100 MIDDLE DEFAULT 60 9 LEFT
36 100 MIDDLE Header-Blue 65 10 MIDDLE
3 100 MIDDLE Pgph-1.25 70 11 LEFT
1034 100 MIDDLE DEFAULT 75 12 MIDDLE
1012 100 MIDDLE SubHeader-Red-L 80 13 LEFT
1014 100 MIDDLE Paragraph 85 14 MIDDLE
2 100 MIDDLE SubHeader 90 15 LEFT
985 100 RIGHT Pgph-Mini-Black 95 16 MIDDLE

(16 row(s) affected)
*/
[/code]

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

rico1931
Starting Member

37 Posts

Posted - 2009-05-22 : 11:17:13

yes this is exactly what I need. I need that FAR RIGHT column to show up in the REGION column. Including the RIGHT Region. Could you possibly explain how you did this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:25:49

is it the last line to show RIGHT ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:35:08

like this ?


;WITH data ([ID], [KEY], [REGION], [OTHERS], [SEQ_NUM], [ROW_NO], [REV_ROW_NO])
AS
(
	SELECT	[ID], [KEY], [REGION], [OTHERS], [SEQ_NUM],
		[ROW_NO] 	= ROW_NUMBER() OVER (ORDER BY SEQ_NUM),
		[REV_ROW_NO]	= ROW_NUMBER() OVER (ORDER BY SEQ_NUM DESC)
	FROM	@data
)
SELECT	*, CASE WHEN REV_ROW_NO = 1 THEN 'RIGHT'
		WHEN ROW_NO % 2 = 1 THEN 'LEFT' 
		ELSE 'MIDDLE' 
		END
FROM	data
ORDER BY ROW_NO

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

rico1931
Starting Member

37 Posts

Posted - 2009-05-22 : 11:59:33

thanks khtan this really helped

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources