Order by Not Working - 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 2005 Forums

Transact-SQL (2005)

Order by Not Working

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-11-11 : 23:24:07

Gurus,

I am trying to do order by on a calculated field but not getting expected results...

Column1
I072
US024
I0155
ILL04
I055

Desired Result:
I 55
I 72
I 155
US 24
ILL 4

How do I get this result?

Thanks in advance.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-12 : 03:41:33

What's the logic for that order?
What's the calculation done to the field? Just replacing '0' with ' '?

--
Gail Shaw
SQL Server MVP

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-12 : 03:49:47

Hi Gail,

What i understand the order by logic is:

First replace the 0 with ''
Second Arrange the order base on length of character (excluding numbers after ' ' ) & then sorting them
Third based on the number part.

My understanding may be wrong also

Regards,
Pramod

I am here to learn from Masters and help new bees in learning.

kellog1
Starting Member

35 Posts

Posted - 2010-11-12 : 09:58:48

Here is the Logic:

First all 'I' should be ordered then 'US' and then 'ILL' and remove the leading zeroes before 'I','US', 'ILL'.

--
Gail Shaw
SQL Server MVP
[/quote]

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-12 : 10:11:10

Will there ever be any other prefixes other than I, US and ILL? If so, how should they be handled?
I assume you mean the zeroes after the I, US and ILL?

--
Gail Shaw
SQL Server MVP

kellog1
Starting Member

35 Posts

Posted - 2010-11-12 : 12:28:32

These are the only prefixes. Yes I am talking about Zereos after the prefixes I, US and ILL.

Basically I am trying to concatenate two fields and doing ORDER BY but I am not getting expected results.

Here what my Table look like...

Col1 Col2
I 024
I 072
I 155
US 036
ILL 004

Desired Result:

Concatenate Col1 and Col2

DesiredColumnValues:
I 24
I 72
I 155
US 36
ILL 4

quote:
Originally posted by GilaMonster

Will there ever be any other prefixes other than I, US and ILL? If so, how should they be handled?
I assume you mean the zeroes after the I, US and ILL?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-12 : 12:46:51

Ah, good, that's far, far easier than what you initially posted (a single column that would have to be parsed apart)
There's no sort order that puts U before I alphabetically. so it's hardly surprising SQL can't intuit what order you want the rows in.

SELECT Col1 + ' ' + REPLACE(Col2, '0', '') AS ConcatenatedColumn
FROM SomeTable
ORDER BY
	CASE Col1
		WHEN 'I' THEN 1
		WHEN 'US' THEN 2
		WHEN 'ILL' THEN 3
		ELSE 1000000
	END, Col2

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources