Is there a better way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is there a better way to do this?

Author

Topic

brokedowngames
Starting Member

5 Posts

Posted - 2011-07-28 : 19:34:54

What I'm trying to do is to create a sorted hierarchy from a single table. The table includes Ids, a parentId, and a sort value among other columns. Basically, I start with the highest
hierarchy and grab those values and sort them. Then for each of those I add it to finalized list and grab the children, sort them and then do the same thing for their children.
Some sample data from MyTable:


ID	SortValue	ParentId	ElementType
5	2		0		1
1	5		5		2
2	6		5		2
8	7		1		3
4	6		2		3

After all is said and done @Final should contain the following:

As you can see each level of hierarchy has its own micro-sorting.
There has to be a better way of doing this. Coming from a C++/C# background, I can't help but think in terms of iteration, but I know this strategy is frowned up in SQL.

DECLARE @MasterList Table
(
	id int,
	sortvalue int,
	parentid int,
	ElementType int
)

DECLARE @Level1 Table
(
	seq int identity(1, 1),
	id int
)

DECLARE @Level2 Table
(
	seq int identity(1, 1),
	id int
)

DECLARE @Level3 Table
(
	seq int identity(1, 1),
	id int
)

DECLARE @Final Table
(
	seq int identity(1, 1),
	id int
)

insert into @MasterList
select
id,
sortvalue,
ParentId,
ElementType
from MyTable

insert into @Level1
select id
from @MasterList
where ElementType = 1
order by sortvalue

while exists(select top 1 * from @Level1)
begin
	declare @level1Id int
	select top 1 @level1Id = id from @Level1

	insert into @Final
	select @level1Id

	delete @Level2

	insert into @Level2
	select id
	from @MasterList e
	where e.parentid = @level1Id
	order by sortvalue

	while exists(select top 1 * from @Level2)
	begin
		declare @level2Id int
		select top 1 @level2Id = id from @Level2

		insert into @Final
		select @level2Id

		delete @Level3

		insert into @Level3
		select id
		from @MasterList e
		where e.parentid = @level2Id
		order by sortvalue

		while exists(select top 1 * from @Level3)
		begin
			declare @level3Id int
			select top 1 @level3Id = id from @Level3

			insert into @Final
			select @level3Id

			delete from @Level3
			where id = @level3Id
		end

		delete from @Level2
		where id = @level2Id
	end

	delete from @Level1
	where id = @level1Id
end

select * from @Final

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-28 : 22:41:56

[code]
; with rcte as
(
select lvl = 1, id, sort = convert(varchar(max), '1')
from MyTable
where Parentid = 0

union all

select lvl = r.lvl + 1, id = t.id, sort = convert(varchar(max), r.sort) + convert(varchar(max), row_number() over (order by t.sortvalue))
from rcte r
inner join MyTable t on r.id = t.Parentid
)
select seq = row_number() over (order by sort),
*
from rcte
order by seq
[/code]

note : the lvl is only to show the hierarchy of the id

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

brokedowngames
Starting Member

5 Posts

Posted - 2011-07-28 : 23:06:09

quote:

Originally posted by khtan


; with rcte as
(
	select	lvl = 1, id, sort = convert(varchar(max), '1')
	from	MyTable
	where	Parentid	= 0

	union all

	select	lvl = r.lvl + 1, id = t.id, sort = convert(varchar(max), r.sort) + convert(varchar(max), row_number() over (order by t.sortvalue))
	from	rcte r
		inner join MyTable t	on	r.id	= t.Parentid
)
select	seq = row_number() over (order by sort),
	*
from	rcte
order by seq

note : the lvl is only to show the hierarchy of the id

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

Thanks for your reply.
I thought maybe a CTE was the way to go. Unfortunately this isn't quite what I need because this doesn't intertwine the results. This gets everything at level 1, sorts, then everything at level 2 sorts, etc.
What I require is to sort level 1 and after each level 1 entry have sorted child level 2 entries and after each level 2 entry have sorted level 3 entries.
So what it should look like is something like this:


Level1
  Level2
    Level3
    Level3
    Level3
  Level2
    Level3
    Level3
Level1
  Level2
    Level3
    Level3
  Level2
    Level3

Where the Level1 are sorted, Level2 are sorted under their respective Level1 parent and Level3 are sorted under their respective Level2. Sorry if my original example wasn't clear. Maybe this makes more sense. I still think maybe there's a way to make a CTE for this, but given my limited SQL knowledge it eludes me.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-28 : 23:09:44

did you try the query ? it does not give the required result ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-28 : 23:40:58

on 2nd look ... the sorting is bit tricky, let me take a look at it again.

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

brokedowngames
Starting Member

5 Posts

Posted - 2011-07-28 : 23:42:38

quote:
Originally posted by khtan

did you try the query ? it does not give the required result ?

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

Ah, it actually is very close; I just didn't recognize immediately what was going on. I see it's doing a string comparison so that the hierarchy would be 1 11 111 112 2 21 etc which works well. The problem is if you have more than 9 rows for a single grouping. Then you end up with something like:
1 11 110 where the 110 is actually a the 10th level 2 in the level 1 grouping. I guess the way to fix this would be to extract out it's primary sort and have it stored as an integer or something like that.

Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-28 : 23:48:43

ya ... close but not closed enough

still have flaws on the sorting . .

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

brokedowngames
Starting Member

5 Posts

Posted - 2011-07-29 : 00:06:26

quote:
Originally posted by khtan

ya ... close but not closed enough still have flaws on the sorting . .

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

Something that is giving slightly better results although it looks tacky is:


select	lvl = r.lvl + 1, id = t.id, sort = convert(varchar(max), convert(int, r.sort) * 10 + row_number() over (order by t.sortvalue))

This still wouldn't work if there were more than 99 rows on each, but I guess one I can find a number that is high enough to use this if I have to. Is there maybe a way to integrate rowcount into this to get a multiplier?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-29 : 02:43:25

[code]
; with rcte as
(
select lvl = 1, id, root = id, sort = convert(varchar(max), '00001')
from MyTable
where Parentid = 0

union all

select lvl = r.lvl + 1, id = t.id, root = r.root,
sort = convert(varchar(max), r.sort)
+ right('00000' + convert(varchar(max), row_number() over (order by t.sortvalue)), 5)
from rcte r
inner join MyTable t on r.id = t.Parentid
)
select seq = row_number() over (order by sort),
*
from rcte
order by root, seq
[/code]

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

brokedowngames
Starting Member

5 Posts

Posted - 2011-07-29 : 10:08:56

Thanks for your help khtan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources