sort sequence - 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

General SQL Server Forums

New to SQL Server Programming

sort sequence

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-28 : 00:04:14

hey all,
im having some problem to do this. i have this one table, that has sequence for other table.


table : tblpicksequence (this sequence is dynamic eg : Non=1, cons=2, RET=3)
picktype	picksequence	itemref
RET		1		x1
Non		2		x1
Cons		3		x1

i need to select record from other table depend on tblpicksequence sequence


table : tbldetail
id	RET	Non	Cons
1	0	0	1
2	1	1	0
3	1	0	0
4 	0	0	1

so base on tblpicksequence, RET=1, NOn=2, Cons=3, i need to list the record from tbldetail :-
i.list all RET=1
ii.if (i) not exists list all Non=1
iii.if (ii) not exists list all Cons=3
i expect :-


id	RET	Non	Cons	picksequence	picktype
2	1	1	0	1		RET
3	1	0	0	1		RET

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 01:26:49

Something like this?

-- Prepare sample data
DECLARE	@PickSequence TABLE (PickType VARCHAR(4), PickSequence TINYINT, ItemRef VARCHAR(2))

INSERT	@PickSequence
SELECT	'RET', 1, 'x1' UNION ALL
SELECT	'Non', 2, 'x1' UNION ALL
SELECT	'Cons', 3, 'x1'

DECLARE	@Detail TABLE(ID TINYINT, Ret TINYINT, Non TINYINT, Cons TINYINT)

INSERT	@Detail
SELECT	1, 0, 0, 1 UNION ALL
SELECT	2, 1, 1, 0 UNION ALL
SELECT	3, 1, 0, 0

-- Show the expected output
SELECT		ID,
		RET,
		Non,
		Cons,
		PickSequence,
		PickType
FROM		(
			SELECT		TOP 1 WITH TIES
					ID,
					RET,
					Non,
					Cons,
					PickSequence,
					PickType
			FROM		(
						SELECT		d.ID,
								d.RET,
								d.Non,
								d.Cons,
								ps.PickSequence,
								ps.PickType
						FROM		@PickSequence AS ps
						INNER JOIN	@Detail AS d ON d.Ret > 0
						WHERE		ps.PickType = 'RET'

						UNION ALL

						SELECT		d.ID,
								d.RET,
								d.Non,
								d.Cons,
								ps.PickSequence,
								ps.PickType
						FROM		@PickSequence AS ps
						INNER JOIN	@Detail AS d ON d.Non > 0
						WHERE		ps.PickType = 'Non'

						UNION ALL

						SELECT		d.ID,
								d.RET,
								d.Non,
								d.Cons,
								ps.PickSequence,
								ps.PickType
						FROM		@PickSequence AS ps
						INNER JOIN	@Detail AS d ON d.Cons > 0
						WHERE		ps.PickType = 'Cons'
					) AS x
			ORDER BY	PickSequence
		) AS y
ORDER BY	ID

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-28 : 02:05:38

yes peso exactly.. (need some result checking).. but like usual.. it takes sometimes for me to understand the code :( and apply it!

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-28 : 02:06:19

wait ,. what is TOP 1 WITH TIES ? the WITH TIES??

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 02:14:24

TOP 1 only select the first row according to the ORDER BY statement.
WITH TIES selects additional records, as long as they are equal to first record according to ORDER BY statement.

All keywords are explained in Books Online.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 02:17:07

If order of ID doesn't matter, you can simplify the query to this

SELECT		TOP 1 WITH TIES
		ID,
		RET,
		Non,
		Cons,
		PickSequence,
		PickType
FROM		(
			SELECT		d.ID,
					d.RET,
					d.Non,
					d.Cons,
					ps.PickSequence,
					ps.PickType
			FROM		@PickSequence AS ps
			INNER JOIN	@Detail AS d ON d.Ret > 0
			WHERE		ps.PickType = 'RET'

			UNION ALL

			SELECT		d.ID,
					d.RET,
					d.Non,
					d.Cons,
					ps.PickSequence,
					ps.PickType
			FROM		@PickSequence AS ps
			INNER JOIN	@Detail AS d ON d.Non > 0
			WHERE		ps.PickType = 'Non'

			UNION ALL

			SELECT		d.ID,
					d.RET,
					d.Non,
					d.Cons,
					ps.PickSequence,
					ps.PickType
			FROM		@PickSequence AS ps
			INNER JOIN	@Detail AS d ON d.Cons > 0
			WHERE		ps.PickType = 'Cons'
		) AS x
ORDER BY	PickSequence

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-28 : 02:19:00

yup.. i removed that oso.. i understand now.. thank u so much.. :) awesome@

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 02:21:36

Maya_Zakry, you have now done over 180 postings.
Your explanation was good and also you provided expected output. Great!

BUT...

In the future, please post sample data in this format

-- Prepare sample data
DECLARE	@PickSequence TABLE (PickType VARCHAR(4), PickSequence TINYINT, ItemRef VARCHAR(2))

INSERT	@PickSequence
SELECT	'RET', 1, 'x1' UNION ALL
SELECT	'Non', 2, 'x1' UNION ALL
SELECT	'Cons', 3, 'x1'

DECLARE	@Detail TABLE(ID TINYINT, Ret TINYINT, Non TINYINT, Cons TINYINT)

INSERT	@Detail
SELECT	1, 0, 0, 1 UNION ALL
SELECT	2, 1, 1, 0 UNION ALL
SELECT	3, 1, 0, 0

so we do not have to reformat it for you.

OK?

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-28 : 02:29:36

OK.. no prob thanks :)

~~~Focus on problem, not solution~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources