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 2008 Forums

Transact-SQL (2008)

Sorting problem

Author

Topic

b98jongu
Starting Member

2 Posts

Posted - 2011-07-13 : 05:20:36

Hi!

I have a sorting problem that I hope you can help me with. My task is to sort a table in two different ways, first all
elements with a Code containing only two digits and the name in upper case (root nodes) should be sorted by Code, then all
rows starting with the root-nodes number (sub-nodes) shold be sorted by Name.

Given this short exempel of my tabel:


Code		Name
------------	---------------
10		HUMANIORA
10.0.0.0.100    Barnkultur
10.0.0.0.400	Adamologi
10.0.0.0.500	Forlagskunskap
20		KONSTNARLIGA
20.0.0.0.100	Takkunskap
20.0.0.0.1000	Bild
20.0.0.0.1100	Solistutbildning

My goal is to have it sorted in the following way:



Code		Name
------------	---------------
10		HUMANIORA        <- Sorted by code (10 before 20)
10.0.0.0.400	Adamologi
10.0.0.0.100	Barnkultur       <- Sorted by name (Adamologi before Barnkultur)
10.0.0.0.500	Forlagskunskap
20		KONSTNARLIGA
20.0.0.0.1000	Bild
20.0.0.0.1100	Solistutbildning
20.0.0.0.100	Takkunskap

I will be a happy man if this could be fixed in SQL so I don´t have to mess around with the data in my application code.

Thanks!

/Jonas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-13 : 05:28:25

Try:

order by
left(Code,2),
case len(Code) when 2 then 1 else 2 end,
Name

No, you're never too old to Yak'n'Roll if you're too young to die.

b98jongu
Starting Member

2 Posts

Posted - 2011-07-13 : 05:39:54

Thanks! That workt beautifylly!

Why is always the solution to a sql problem so damn simple once you see it

quote:
Originally posted by webfred

Try:

order by
left(Code,2),
case len(Code) when 2 then 1 else 2 end,
Name

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-13 : 06:03:47

Oh I know that feeling

You're welcome.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources