June 13, 2012 at 6:28 am
Hello all,
I need to order the resultset from a query in a specific way, but I haven't got a clue how to achieve this.
Select * from table gives me the result below:
level parent child info
1 1234 1237 some remarks
1 1234 1238 extra remarks
1 1234 1239 more remarks
2 1237 1240 even more remarks
2 1237 1241 blablabla
3 1240 1242 bladibladibla
Is it possible to order it this way?
level parent child info
1 1234 1237 some remarks
2 1237 1240 even more remarks
3 1240 1242 bladibladibla
2 1237 1241 blablabla
1 1234 1238 extra remarks
1 1234 1239 more remarks
In the second way of sorting a parent number appears directly underneath the corresponding number in the child column.
How should the T-SQL command look to achieve this?
Thanks,
Wessel
June 13, 2012 at 6:33 am
Is it possible to order it this way?
1 1234 1237 some remarks
2 1237 1240 even more remarks
3 1240 1242 bladibladibla
2 1237 1241 blablabla
1 1234 1238 extra remarks
1 1234 1239 more remarks
There's got to be more that you're not showing. Otherwise, how do you know that "[font="Courier New"]2 1237 1241 blablablah[/font]" isn't supposed to be the second record in the order?
June 13, 2012 at 6:49 am
Sample data for use: -
SELECT level, parent, child, info
INTO #yourTable
FROM (VALUES(1,1234,1237,'some remarks'),
(1,1234,1238,'extra remarks'),
(1,1234,1239,'more remarks'),
(2,1237,1240,'even more remarks'),
(2,1237,1241,'blablabla'),
(3,1240,1242,'bladibladibla'))a(level, parent, child, info);
That makes it easier for people to help you. Now, using the above sample data we can do this: -
SELECT level, parent, child, info
FROM (SELECT a.level, a.parent, a.child, a.info,
ROW_NUMBER() OVER(PARTITION BY a.parent ORDER BY b.parent)
FROM #yourTable a
OUTER APPLY (SELECT level, parent, child, info
FROM #yourTable
WHERE a.parent = child) b
)innerQ(level, parent, child, info, pos)
ORDER BY pos;
Which produces: -
level parent child info
----------- ----------- ----------- -----------------
1 1234 1237 some remarks
2 1237 1240 even more remarks
3 1240 1242 bladibladibla
2 1237 1241 blablabla
1 1234 1238 extra remarks
1 1234 1239 more remarks
June 13, 2012 at 6:49 am
You are correct, I overlooked one column:
level parent child pos info
1 1234 1237 1 some remarks
1 1234 1238 2 extra remarks
1 1234 1239 3 more remarks
2 1237 1240 1 even more remarks
2 1237 1241 2 blablabla
3 1240 1242 1 bladibladibla
should look like:
level parent child pos info
1 1234 1237 1 some remarks
2 1237 1240 1 even more remarks
3 1240 1242 1 bladibladibla
2 1237 1241 2 blablabla
1 1234 1238 2 extra remarks
1 1234 1239 3 more remarks
The position column isn't necessarily sorted ascending, because it can happen that
3 1240 1242 1 bladibladibla
is a level below parent 1241 instead of 1240.
Hope this clears it up.
June 14, 2012 at 2:20 am
Thanks for the replies.
They helped me, but its still a difficult topic for me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply