Order This - 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

SQL Server Development (2000)

Order This

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2005-03-22 : 12:30:12

Given the following


DECLARE @TABLE TABLE(
descr VARCHAR(100)
)



INSERT INTO @TABLE
SELECT 'Jo'
UNION SELECT 'Blank'
UNION SELECT 'Group 1'
UNION SELECT 'Group 2'
UNION SELECT 'Group 10'

I want output that makes sense to a human, so the order I would expect if I was to order it by hand would be

Blank
Group 1
Group 2
Group 10
Jo


SELECT * FROM @TABLE

SELECT * FROM @TABLE ORDER BY descr

Obviously dont quite do the trick!

So, I have google'd and tried the following



DECLARE @maxlength INTEGER
SELECT @maxlength = NULL
SELECT
	@maxlength = ISNULL(MAX(LEN([descr])),0)
FROM
	@TABLE

SELECT 
	*, RIGHT(REPLICATE('0', @maxlength) + [descr], @maxlength)
FROM 
	@TABLE
ORDER BY 
	RIGHT(REPLICATE('0', @maxlength) + [descr], @maxlength)

Which gets the "Group" section ordering OK, but still is not really right.

I struggle with this occassionally and have posted similar in the past and have always managed to ignore the problem (or implement a positional system, e.g. give each entry a position and allow them to be moved around, which is a bit clumsy).

But it has bitten me again and so if anyone can explain a way to get the order working successfully regardless of what is in the table it would be great

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 12:59:54

try this:


DECLARE @TABLE TABLE(
descr VARCHAR(100)
)

INSERT INTO @TABLE
SELECT 'Jo'
UNION SELECT 'Blank'
UNION SELECT 'Group 1'
UNION SELECT 'Group 2'
UNION SELECT 'Group 10'

DECLARE @maxlength INTEGER
SELECT 	@maxlength = NULL
SELECT
	@maxlength = ISNULL(MAX(LEN([descr])),0)
FROM
	@TABLE

SELECT 
	*, RIGHT(REPLICATE('_', @maxlength) + [descr], @maxlength)
FROM 
	@TABLE
ORDER BY 
	substring(descr,1,1), RIGHT(REPLICATE('_', @maxlength) + [descr], @maxlength)

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-22 : 13:03:11

My best guess is that you'd need a udf using charindex and would have to parse out the numerics....

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-22 : 13:30:54

and of course the best solution involves storing your data a little better. Either add a numeric sort column to your table, or break your current column out into 2 columns -- one for the name ("Group") and one for the number. Then sorting is handled fine by sorting by name first and then by number (numerically), and to display the name as if it were all 1 column you just concatenate them together.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 13:36:50

as much as i agree with you jeff that's not always that simple

my guess is that he simply has a varchar column that can have any text in it.
why is it like that and what's the poitn of it is of course another matter

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-22 : 14:59:13

Nope, I agree with Jeff on this.

If your req is that the varchar data needs to be sorted by a number, then the order priority is part of the logical requirements, and as such is an attribute of a thing. The description is another attribute.

By forcing the deconstruction of a string to meet certain rules, you're breaking the rules of normalization.

Plus how confident can you be in those rules when the data is not constrained in any manner. The rules are gaurenteed to break at some point.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources