Thread: Sorting Numerically, and Alpha Numerically

1. Registered User
Join Date
Jun 2003
Location
Ottawa
Posts
105

Unanswered: Sorting Numerically, and Alpha Numerically

Hi,

I'm hoping there is an easy way to deal with this....

I have a UNION query. The first SELECT in the UNION I want sorted by an int, the the second SELECT in the union (same column) I want sorted by a char.

Here's a really basic sample of what I mean:

select dummy_data.test from
(select cast(1 as char) as test
union
select cast(11 as char) as test
union
select cast(2 as char) as test
union
select 'Alpha' as test
union
select 'Bravo' as test) dummy_data
order by test asc

..obviously, I need to cast the INT columns as chars or the UNIONs do not work.

However, the sort will sort the numbers alpha numerically: 1,11,2
(I'm looking for 1,2,11)

Any thoughts?

david.

2. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
as far as i'm aware you've got to cast them back again letter are sorted Alpha numeric while numbers are Numeric.

maybe
order by cast(test as int) asc

this is off the top of my head, untested and put together on the fly

3. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
okay, I'm sure this is bass-ackwards, and the blind dude or Brett will laugh, but it works...

I know that you can't have multiple order bys in a union, it only acts on the last one. Therefore, you have to query the numeric first, sort it, then convert it to char. Then add in the sorted Alpha data.

Code:
```SELECT TEST1 FROM (
SELECT CAST(A.TEST AS CHAR) AS TEST1 FROM (
select TOP 100 PERCENT 1 as test
union
select TOP 100 PERCENT 11 as test
union
select TOP 100 PERCENT 2  as test
ORDER BY TEST ASC) A) B
UNION ALL
select test from (
select top 100 PERCENT 'Alpha' as test
union
select top 100 PERCENT 'Bravo' as test
ORDER BY TEST ASC) c```

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
why are they sharing the same column in the output, please?

separate columns makes everything so much simpler --
Code:
```select col1
, cast(null as integer) as numeric_col
, alpha_col
from table_with_alpha_data
union all
select col1
, numeric_col
, null
from table_with_numeric_data
order
by alpha_col
, numeric_col```
for bonus points, explain what happens with the NULLs in the sort sequence

5. Registered User
Join Date
Jun 2003
Location
Ottawa
Posts
105
thanks RedNeckGeek ....that suggestion does work! thanks.

r937.....long story, but it involves a somewhat inflexible BI tool I'm working with.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•