Ordering numbers first then strings 4

[StevenK]

I'm not sure if this is something that is possible - or whether it's something simple I'm overlooking. ;-)

I've got a table TABLE1 with a SERIAL_NO field in it.
This contains a string value.
Sometimes this SERIAL_NO field contains a number - i.e. '1000', '2999' - and sometimes it contains a string - i.e. 'AA001', 'ZZ001'

Is there any means by which I can order the result set - so that the numbers come first (in numerical order), then the strings.
When I ORDER BY SERIAL_NO it clearly treats it as a string (as it is) and I get orderings like:
100
1001
200
299
300
999
Ideally I want these to be in numerical order, so that 1001 follows after 999.
But I can't simply CAST this field as a number - as sometimes it contains a string value.

So I want the ordering to be something like:
100
200
999
1001
AA001
ZZ001

How can I go about doing this?
Can it be done?

Thanks in advance.
Steve

 

[Crowley16]

there's probably an easier way, but off the top of my head...

use a union query, the first only brings back numeric values, the second only brings back text values, e.g.

select
serial
from
table
where
isnumeric(serial)
order by
cast(serial as int)

union select
serial
from
table
where
not isnumeric(serial)

or something of the sort...

--------------------
Procrastinate Now!

 

[ca8msm]

How about:

[COLOR=blue]declare[/color] @table [COLOR=blue]table[/color] (serialno [COLOR=blue]varchar[/color](12))

[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'100'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'1001'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'200'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'188'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'ZZ001'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'AA001'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'300'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'999'[/color])

[COLOR=blue]select[/color] serialno
[COLOR=blue]from[/color] @table
[COLOR=blue]order[/color] [COLOR=blue]by[/color] 
	[COLOR=blue]case[/color] 
		[COLOR=blue]when[/color] [COLOR=#FF00FF]isnumeric[/color](serialno) = 0 [COLOR=blue]then[/color] 999999999999
		[COLOR=blue]else[/color] [COLOR=#FF00FF]convert[/color](bigint,serialno) 
	[COLOR=blue]end[/color]
	,[COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](12),serialno)

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[k01]

serial : varchar(5)

select
serial
from
table
order by
right('00000' + serial,5)

 

[ca8msm]

order by
right('00000' + serial,5)

Nice solution . Only issue I see is if you have numbers greater than 5 digits then it would produce incorrect results e.g.

[COLOR=blue]declare[/color] @table [COLOR=blue]table[/color] (serialno [COLOR=blue]varchar[/color](12))

[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'100'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'1001'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'200'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'188'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'ZZ001'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'AA001'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'300'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'899991'[/color])
[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'799991'[/color])

[COLOR=blue]select[/color] serialno, [COLOR=#FF00FF]right[/color]([COLOR=red]'00000'[/color] + serialno,5)
[COLOR=blue]from[/color] @table
[COLOR=blue]order[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]right[/color]([COLOR=red]'00000'[/color] + serialno,5)

I guess you could easily increase this number and the leading zeros to a larger one though based on what your table will contain, so that makes it a very quick and easy solution.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[AlexCuse]

Try adding this to the sample data though:

[COLOR=blue]insert[/color] @table [COLOR=blue]values[/color] ([COLOR=red]'A2'[/color])

I'm no expert, but I think that the case statement is the way to go here, especially if you *need* consistent results.

However, per this FAQ (faq183-6423) I would change the case statement to:

[COLOR=blue]order[/color] [COLOR=blue]by[/color] 
[COLOR=blue]case[/color] 
[COLOR=blue]when[/color] [COLOR=#FF00FF]isnumeric[/color](serialno [b]+ [COLOR=red]'e0'[/color][/b]) = 0 [COLOR=blue]then[/color] 999999999999
[COLOR=blue]else[/color] [COLOR=#FF00FF]convert[/color](bigint,serialno) 
[COLOR=blue]end[/color]

(And I would even more strongly recommend writing your own function as outlined in the FAQ's)

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[bborissov]

Mark,
k01 gave the example with 5 char length. If you need more, just change it:

declare @table table (serialno varchar(12))

insert @table values ('100')
insert @table values ('1001')
insert @table values ('200')
insert @table values ('188')
insert @table values ('ZZ001')
insert @table values ('AA001')
insert @table values ('300')
insert @table values ('899991')
insert @table values ('799991')
DECLARE @zeroes varchar(12)
SET @zeroes = REPLICATE('0',12)

select serialno, right(@zeroes + serialno,12)
       from @table
order by right(@zeroes + serialno,12)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[StevenK]

Using the ideas here I've been able to structure the sql stored procedure to give the results in the desired order.
Thanks for everyone's help with this.
You guys are stars!!!

 

[memdiscount]

Hi;

The simplest answer of this question could be, just use Asc in order by.

Thanks

See sample:

declare @table table (serialno varchar(12))

insert @table values ('100')
insert @table values ('1001')
insert @table values ('200')
insert @table values ('188')
insert @table values ('ZZ001')
insert @table values ('AA001')
insert @table values ('300')
insert @table values ('899991')
insert @table values ('799991')

select serialno
from @table
order by serialno Asc

Thanks

Essa Mughal
Toronto, Canada

 

[StevenK]

Using this code sample gives the exact problem I started with - it shows me the result set in the order:
100
1001
188
200
300
799991
899991
AA001
ZZ001
I would want 1001 to come below 300 (and 999 if it were there).
The suggestions previously made were useful and I have been able to resolve the issue in this way.
Thanks again.

 

[ca8msm]

The simplest answer of this question could be, just use Asc in order by.

Your simple answer produces incorrect results.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[memdiscount]

Hello;

I appreciate the way "StevenK" reply to my post. That requirement was totally overlooked by me. I apologize for that.

But, we should not reply like the way the above post was sent.

Thanks





Essa Mughal
Toronto, Canada

 

[ca8msm]

But, we should not reply like the way the above post was sent.

There was nothing untoward in my post. I was just stating a simple fact that it produced incorrect results. Don't take it personally.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]