Sorting problem - 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 2000 Forums

Transact-SQL (2000)

Sorting problem

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2004-04-28 : 16:57:59

I think this is an easy problem but I just can't seem to figure it out. I was attempting a solution using derived tables.
I have (sample data):

Table (Menu)
Parent_ID, ID, Name
0 1 Administration
0 2 Options
1 3 Admin 1
1 4 Admin 2
2 5 Option 1
2 6 Option 2
1 7 Admin 3

I want:

Administration
Admin 1
Admin 2
Admin 3
Options
Option 1
Option 2

This sorts by Parent ID, but for every root parent (ie parent ID 0), it then sorts by the children with that parent ID before continuing with the next root parent.

Any pointers would be wonderful!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-28 : 17:09:21

Does this work for you?

DROP TABLE #menu

CREATE TABLE #menu(
Parent_ID INT,
ID INT,
Name VARCHAR(55))

INSERT #menu(
Parent_ID,
ID,
Name)

SELECT 0, 1, 'Administration'
UNION ALL
SELECT 0, 2, 'Options'
UNION ALL
SELECT 1, 3, 'Admin 1'
UNION ALL
SELECT 1, 4, 'Admin 2'
UNION ALL
SELECT 2, 5, 'Option 1'
UNION ALL
SELECT 2, 6, 'Option 2'
UNION ALL
SELECT 1, 7, 'Admin 3'

SELECT Name
FROM #menu
ORDER BY
CASE Parent_ID WHEN 0 THEN ID ELSE Parent_ID END,
ID

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Phibian
Starting Member

32 Posts

Posted - 2004-04-28 : 17:21:33

I didn't know that you could put CASE statements in an ORDER BY, but I'll try it. Thanks!

Isn't there another way without using a CASE statement though?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-28 : 17:28:21

There's about a million ways. The CASE is pretty efficient. Remember the ORDER BY gets evaluated and performed after the recordset so the overhead doing it this way can be better than doing a fancy nested join.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Phibian
Starting Member

32 Posts

Posted - 2004-04-28 : 18:47:22

I agree that the CASE is going to be the most efficient.

But can anyone suggest one of the other million ways? If I go with the CASE statement, it will be efficient but not portable, and under the circumstances portability is probably more desirable than efficiency since the number of records in the particular table is quite low.

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2004-04-29 : 06:43:16

Try this one? and check it up this will work for another set of data?

select name from menu order by left(name,1),id

vijayakumar_svk@hotmail.com

Work smarter not harder take control of your life be a super achiever

Phibian
Starting Member

32 Posts

Posted - 2004-04-30 : 12:16:29

That seems to just sort alphabetically.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-30 : 12:45:51

How is it not portable? You will always create the category before the sub-categories. So, I can't think of an instance this would not work. Can you? The other options are to go with nested tables, temp tables, or redesign of the structure to have a nested hierarchy using recursive keys.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Phibian
Starting Member

32 Posts

Posted - 2004-04-30 : 13:28:36

CASE statements aren't portable because they are SQL Server specific and we use a bunch of different databases as well (MS Access, PostGreSQL to name a couple).

The category is always created before the sub-category, unless the sub-category originally belongs to a different category and is later moved to a new one. But the CASE statement seems to still work in that case. The issue is really trying to get a solution that works more generally.

"The other options are to go with nested tables, temp tables, or redesign of the structure to have a nested hierarchy using recursive keys."
I'd have to look up nested tables (not something I've done before) but wouldn't I have the same problem using a temp table? In order to get the info into the temp table, I'd have to be able to select the parent and all its children.

I could fix the problem simply by adding a column by which to sort and giving the parent and child the same sort number (probably what I'm going to do). But I still have this gnawing feeling at the back of my mind that there is a way to alternate results somehow so that you can subselect all children records in between parents, without needing a CASE.

Ah well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources