Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unanswered: query solution...

    I have need of a query solution to sort results by one field, alphabetically then numerically...


    sample data

    I0 42.50
    I1 42.50b
    MD Certified
    Extreme Systems

    its a description field

    thanks

  2. #2
    Join Date
    Dec 2001
    Location
    India
    Posts
    25
    Hi

    check out this

    select column_name from table_name
    where column_name like '[a-z]%'
    union all
    select column_name from table_name
    where column_name like '[0-9]%'

    Hope this will help you...

    Regards,
    Selva Balaji B.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    selva, good thinking, but it's not guaranteed to work the way you expect

    a good optimizer will realize that the FROM and WHERE clauses are the same in both subselects of the UNION, and will therefore satisfy both subselects with the same pass through the table, so the result set will be in whatever order the optimizer happens to scan the table, since you did not specify an ORDER BY

    remember, to get a specific order, you *must* use ORDER BY

    another approach to the original problem is
    Code:
    select case
             when substr(description,1,1) >= '0'
             then 1 else 0
           end as sortkey
         , description
      from thetable
    order by sortkey, description
    this sorts the descriptions that start with a numeric character after all the others

    SUBSTR is usually much faster than LIKE

    rudy
    http://rudy.ca/

Posting Permissions

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