Complex Order By - 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)

Complex Order By

Author

Topic

ashwini.anand
Starting Member

5 Posts

Posted - 2012-02-10 : 04:47:24

I’m trying to do a kind of complex sorting , I can’t seem to get it right.
I tried using “UNION” to select the table. But, I'm not sure how to achieve this.

I have the following table:


S_ID    ME_F    visit_type      visit_desc      period  study_day       dose_time
1158     0       Check In         NULL            1       -1              NULL
1158     1       In Patient       Dose Day	  1        1              NULL
1158     0       Check Out        NULL            1        2              NULL
1158     0       OPV              NULL            1        3              NULL
1158     0       Check In         NULL            2        -1             NULL
1158     0       In Patient       Dose Day        2        1              NULL
1158     0       Check Out        NULL            2        2              NULL
1158     0       OPV              NULL            2        3              NULL
1158     0       Wash Out Min     NULL            1        2              NULL
1158     0       Wash Out Max     NULL            1        4              NULL
1158     0       Wash Out Min     NULL            2        1              NULL
1158     0       Wash Out Max     NULL            2        7              NULL

I want to sort the table :
On Period and Study_Day in chronological Order but also want 'Wash Out Min' and 'Wash Out Max' Visit Types should always be at the last of the period.
(If period is 1, then wash out min and max defined for period 1 should come at last of period 1. Similarly for period 2 , 3 etc.).

Is there any way to achieve this?

Resulting Table should be :


S_ID    ME_F    visit_type      visit_desc      period  study_day       dose_time
1158     0       Check In         NULL            1       -1              NULL
1158     1       In Patient       Dose Day	  1        1              NULL
1158     0       Check Out        NULL            1        2              NULL
1158     0       OPV              NULL            1        3              NULL
1158     0       Wash Out Min     NULL            1        2              NULL
1158     0       Wash Out Max     NULL            1        4              NULL
1158     0       Check In         NULL            2        -1             NULL
1158     0       In Patient       Dose Day        2        1              NULL
1158     0       Check Out        NULL            2        2              NULL
1158     0       OPV              NULL            2        3              NULL
1158     0       Wash Out Min     NULL            2        1              NULL
1158     0       Wash Out Max     NULL            2        7              NULL

The query which I'm using is:

select
t2.study_cohort_map_id,
t2.major_event_flag,
t2.visit_type,
t2.visit_desc,
t2.period_id,
t2.study_day,
t2.dose_time
FROM (
SELECT TOP 100 PERCENT
t0.study_cohort_map_id,
t0.major_event_flag,
VISIT_TYPE_MST.visit_type,
VISIT_DESC_MST.visit_desc,
t0.period_id,
t0.study_day,
t0.dose_time
FROM COHORT_INTERVAL_DTLS AS t0
join VISIT_TYPE_MST on t0.visit_type_id = VISIT_TYPE_MST.visit_type_id
left outer join VISIT_DESC_MST on t0.visit_desc_id = VISIT_DESC_MST.visit_desc_id
WHERE t0.study_cohort_map_id=1158 and t0.active_flag=1 and VISIT_TYPE_MST.visit_type NOT IN ('Wash Out Min','Wash Out Max')
order by t0.period_id,t0.study_day
UNION ALL
SELECT TOP 100 PERCENT
t1.study_cohort_map_id,
t1.major_event_flag,
VISIT_TYPE_MST.visit_type,
VISIT_DESC_MST.visit_desc,
t1.period_id,
t1.study_day,
t1.dose_time
FROM COHORT_INTERVAL_DTLS AS t1
join VISIT_TYPE_MST on t1.visit_type_id = VISIT_TYPE_MST.visit_type_id
left outer join VISIT_DESC_MST on t1.visit_desc_id = VISIT_DESC_MST.visit_desc_id
WHERE t1.study_cohort_map_id=1158 and t1.active_flag=1 and VISIT_TYPE_MST.visit_type IN ('Wash Out Min','Wash Out Max')
order by t1.period_id,VISIT_TYPE_MST.visit_type DESC
) AS [t2];

thank you for your help guys!

Ashwini Anand

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 04:58:14

Does this work?


DECLARE @sample TABLE (
	[S_ID] INT
	, [ME_F] BIT
	, [visit_type] VARCHAR(255)
	, [visit_desc] VARCHAR(255)
	, [period] TINYINT
	, [study_day] SMALLINT
	, [dose_time] TINYINT
	)
INSERT @sample VALUES 
(1158,	0,	'Check In',	NULL,	 1,	-1,	 NULL),
(1158,	1,	'In Patient',	'Dose DAY',	1,	1,	 NULL),
(1158,	0,	'Check Out',	NULL,	 1,	2,	 NULL),
(1158,	0,	'OPV',	 NULL,	 1,	3,	 NULL),
(1158,	0,	'Check In',	NULL,	 2,	-1,	 NULL),
(1158,	0,	'In Patient',	'Dose Day',	2,	1,	 NULL),
(1158,	0,	'Check Out',	NULL,	 2,	2,	 NULL),
(1158,	0,	'OPV',	 NULL,	 2,	3,	 NULL),
(1158,	0,	'Wash Out Min',	NULL,	 1,	2,	 NULL),
(1158,	0,	'Wash Out MAX',	NULL,	 1,	4,	 NULL)


SELECT * FROM @sample
ORDER BY
	[period]
	, CASE WHEN [Visit_Type] IN ('Wash Out Min', 'Wash Out Max') THEN 1 ELSE 0 END
	, [study_day]

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

ashwini.anand
Starting Member

5 Posts

Posted - 2012-02-10 : 05:19:56

I don't think this will be a solution.
Please let me know if you have understood my question.
My requirement is different.

Ashwini Anand

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 05:52:43

Wow.

Yes I understood your question. You said

quote:

I want to sort the table :
On Period and Study_Day in chronological Order but also want 'Wash Out Min' and 'Wash Out Max' Visit Types should always be at the last of the period.
(If period is 1, then wash out min and max defined for period 1 should come at last of period 1. Similarly for period 2 , 3 etc.).

Is there any way to achieve this?

the ordering is as I showed:


SELECT * FROM @sample
ORDER BY
	[period]
	, CASE WHEN [Visit_Type] IN ('Wash Out Min', 'Wash Out Max') THEN 1 ELSE 0 END
	, [study_day]

The code you posted (with UNION) doesn't make any sense. The ORDER BY inside the UNION is not kept in the final result.

So lets go over my suggestion again.

1) The Code : This just puts the sample data you posted into a table so I can run a query on it. Then I run a query on it producing the results you wanted.


DECLARE @sample TABLE (
	[S_ID] INT
	, [ME_F] BIT
	, [visit_type] VARCHAR(255)
	, [visit_desc] VARCHAR(255)
	, [period] TINYINT
	, [study_day] SMALLINT
	, [dose_time] TINYINT
	)
INSERT @sample VALUES 
(1158,	0,	'Check In',		NULL,		1,	-1,	 NULL),
(1158,	1,	'In Patient',	'Dose DAY',	1,	1,	 NULL),
(1158,	0,	'Check Out',	NULL,		1,	2,	 NULL),
(1158,	0,	'OPV',			NULL,		1,	3,	 NULL),
(1158,	0,	'Check In',		NULL,		2,	-1,	 NULL),
(1158,	0,	'In Patient',	'Dose Day',	2,	1,	 NULL),
(1158,	0,	'Check Out',	NULL,		2,	2,	 NULL),
(1158,	0,	'OPV',			NULL,		2,	3,	 NULL),
(1158,	0,	'Wash Out Min',	NULL,		1,	2,	 NULL),
(1158,	0,	'Wash Out MAX',	NULL,		1,	4,	 NULL),
(1158,  0,  'Wash Out Min', NULL,       2,  1,   NULL),
(1158,  0,  'Wash Out MAX', NULL,       2,  7,   NULL)

SELECT * FROM @sample
ORDER BY
	[period]
	, CASE WHEN [Visit_Type] IN ('Wash Out Min', 'Wash Out Max') THEN 1 ELSE 0 END
	, [study_day]

2) The results


(12 row(s) affected)
S_ID        ME_F  visit_type           visit_desc           period study_day dose_time
----------- ----- -------------------- -------------------- ------ --------- ---------
1158        0     Check In             NULL                 1      -1        NULL
1158        1     In Patient           Dose DAY             1      1         NULL
1158        0     Check Out            NULL                 1      2         NULL
1158        0     OPV                  NULL                 1      3         NULL
1158        0     Wash Out Min         NULL                 1      2         NULL
1158        0     Wash Out MAX         NULL                 1      4         NULL
1158        0     Check In             NULL                 2      -1        NULL
1158        0     In Patient           Dose Day             2      1         NULL
1158        0     Check Out            NULL                 2      2         NULL
1158        0     OPV                  NULL                 2      3         NULL
1158        0     Wash Out Min         NULL                 2      1         NULL
1158        0     Wash Out MAX         NULL                 2      7         NULL

which match what you wanted.

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 - 2012-02-10 : 05:58:32

and to spoon feed you. You probably want this as your final SQL


SELECT
	t0.study_cohort_map_id,
	t0.major_event_flag,
	VISIT_TYPE_MST.visit_type,
	VISIT_DESC_MST.visit_desc,
	t0.period_id,
	t0.study_day,
	t0.dose_time
FROM
	COHORT_INTERVAL_DTLS AS t0
	join VISIT_TYPE_MST on t0.visit_type_id = VISIT_TYPE_MST.visit_type_id
	left outer join VISIT_DESC_MST on t0.visit_desc_id = VISIT_DESC_MST.visit_desc_id
WHERE
	t0.study_cohort_map_id=1158
	and t0.active_flag=1
order BY
	t0.period_id
	, CASE WHEN VISIT_TYPE_MST.visit_type IN ('Wash Out Min', 'Wash Out Max') THEN 1 ELSE 0 END
	, t0.study_day

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

ashwini.anand
Starting Member

5 Posts

Posted - 2012-02-10 : 06:29:35

Thanks Charlie.It works.

But I didn't wanted to offend you.
I thought may be that time i didn't posted the topic correctly.
So...... any ways, thanks again.

Ashwini Anand

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 06:32:47

no problem!

Sorry -- I'm a bit grumpy this morning.

Glad it works for you.

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-10 : 07:04:50

Charlie, you forgot to ALL CAPS a LOT of the keywords (JOIN, ORDER, AND, etc) in your formatting. Learn to spoon feed properly, kthks.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 07:21:59

I apologise for that almost unforgivable lapse


SELECT
	cid.[study_cohort_map_id]
	, cid.[major_event_flag]
	, vtm.[visit_type]
	, vdm.[visit_desc]
	, cid.[period_id]
	, cid.[study_day]
	, cid.[dose_time]
FROM
	COHORT_INTERVAL_DTLS AS cid
	JOIN VISIT_TYPE_MST AS vtm ON cid.[visit_type_id] = vtm.[visit_type_id]
	LEFT OUTER JOIN VISIT_DESC_MST AS vdm ON cid.[visit_desc_id] = vdm.[visit_desc_id]
WHERE
	cid.[study_cohort_map_id] = 1158
	AND cid.[active_flag] = 1
ORDER BY
	cid.[period_id]
	, CASE WHEN vtm.[visit_type] IN ('Wash Out Min', 'Wash Out Max') THEN 1 ELSE 0 END
	, cid.[study_day]

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-10 : 10:19:09

You will be forgiven THIS time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources