Results 1 to 5 of 5
  1. #1
    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.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  2. #2
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    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
    Inspiration Through Fermentation

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

Posting Permissions

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