Alternative sorting in a SELECT statement

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)

Alternative sorting in a SELECT statement

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2015-01-15 : 09:07:53

Below are two tables - before and after sorting.
Is it possible to do this kind of 'alternate' sorting?
The main issue seems to be when there are two sequences of 'matching pairs' in the corresponding columns e.g. 5478 and 7627


before

ID	SOURCE_ID
147	NULL
575	NULL
2221	NULL
2791	NULL
5478	NULL
7115	NULL
7242	NULL
7493	NULL
7499	NULL
7500	NULL
7517	NULL
7590	NULL
7595	NULL
7596	NULL
7608	NULL
7609	7595
7610	7517
7611	2791
7612	575
7613	7242
7614	7499
7615	7115
7616	NULL
7617	7616
7619	7500
7620	2221
7621	147
7622	7493
7623	7620
7625	7590
7626	7596
7627	5478
7628	7627
7630	7608

after

ID	SOURCE_ID
147	NULL
7621	147
575	NULL
7612	575
2221	NULL
7620	2221
7623	7620
2791	NULL
7611	2791
5478	NULL
7627	5478
7628	7627
7115	NULL
7615	7115
7242	NULL
7613	7242
7493	NULL
7622	7493
7499	NULL
7614	7499
7500	NULL
7619	7500
7517	NULL
7610	7517
7590	NULL
7625	7590
7595	NULL
7609	7595
7596	NULL
7626	7596
7608	NULL
7630	7608
7616	NULL
7617	7616

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-15 : 09:09:35

You can ORDER BY <expression>. Let your imagination run wild!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-16 : 08:41:34

Recursion will be the easiest way to handle this:


-- *** Consumable Test Data ***
-- Please provide in future
CREATE TABLE #t
(
	ID int NOT NULL
	,Source_ID int NULL
);
INSERT INTO #t
VALUES(147, NULL),(575, NULL),(2221, NULL),(2791, NULL),(5478, NULL)
	,(7115, NULL),(7242, NULL),(7493, NULL),(7499, NULL),(7500, NULL)
	,(7517, NULL),(7590, NULL),(7595, NULL),(7596, NULL),(7608, NULL)
	,(7609, 7595),(7610, 7517),(7611, 2791),(7612, 575),(7613, 7242)
	,(7614, 7499),(7615, 7115),(7616, NULL),(7617, 7616),(7619, 7500)
	,(7620, 147),(7622, 7493),(7623, 7620),(7625, 7590),(7626, 7596)
	,(7627, 5478),(7628, 7627),(7630, 7608);
-- *** End Test Data ***

WITH Hierarchy
AS
(
	SELECT ID, Source_ID
		,ID AS TopID
	FROM #t
	WHERE Source_ID IS NULL

	UNION ALL

	SELECT T.ID, T.Source_ID
		,H.TopID
	FROM #t T
		JOIN Hierarchy H
			ON T.Source_ID = H.ID
)
SELECT ID, Source_ID
FROM Hierarchy
ORDER BY TopID, ID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources