Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    Wiltshire, UK
    Posts
    5

    Unanswered: Sensible ordering

    Ordering data from a query using ORDER BY does not always give nice results as it is the ASCII order.

    For example a list of addresses in a string would come out like:

    1 Place Road
    10 Place Road
    11 Place Road
    2 Place Road
    3 Place Road
    30 Place Road
    31 Place Road

    When it needs to be displayed as

    1 Place Road
    2 Place Road
    3 Place Road
    10 Place Road
    11 Place Road
    30 Place Road
    31 Place Road


    My solution to this is to have an ordering field in the database, but is there a better solution that would work automatically?

  2. #2
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Meaningul ordering

    Hello

    This is the code I used to sort like what you want. Have fun.

    drop table #x
    create table #x(v1 char(50))
    insert into #x select '1 place road'
    insert into #x select '2 place road'
    insert into #x select '10 place road'
    insert into #x select '20 place road'
    insert into #x select '3 place road'
    insert into #x select '30 place road'
    insert into #x select '31 place road'
    select * from #x order by convert(int,substring(v1,1,charindex(' ',v1)))

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Re: Sensible ordering

    RE:
    Ordering data from a query using ORDER BY does not always give nice results as it is the ASCII order.

    For example a list of addresses in a string would come out like:

    1 Place Road
    10 Place Road
    11 Place Road
    2 Place Road
    3 Place Road
    30 Place Road
    31 Place Road

    When it needs to be displayed as

    1 Place Road
    2 Place Road
    3 Place Road
    10 Place Road
    11 Place Road
    30 Place Road
    31 Place Road


    My solution to this is to have an ordering field in the database, but

    Q1 Is there a better solution that would work automatically?
    A1 Generally, yes.

    In a normalized design, one may expect a simple Select statement issued against a view (with an appropriately constructed Order By clause) to automatically return the required result set. Consider further normalising the logical design to better meet the business requirement(s). For example:

    Instead of a Single Address Column in TableAddresses Table:
    (AddressString)
    101 Apple Avenue Apartment A63
    20 Bumblebee Boulevard Building B4
    33 Catbird Circle Condominium C6
    444 Dogleg Drive Dormitory 8
    5 Edgewater Expressway Exit 2g
    6,606 Fox Freeway
    7 Gateway Gorge Parkway
    120 Bumblebee Boulevard
    90 Bumblebee Boulevard
    21 Bumblebee Boulevard

    Consider AddressesView, supported by Columns in AddressesBaseTable and AddressesSubUnitTable:

    AddressesView:
    (Select AddressNumber, AddressName, SubType, SubUnitNumber From AddressesView Order By AddressName, AddressNumber) --> Result Set:
    101 Apple Avenue Apartment A63
    20 Bumblebee Boulevard Building B4
    21 Bumblebee Boulevard
    90 Bumblebee Boulevard
    120 Bumblebee Boulevard
    33 Catbird Circle Condominium C6
    444 Dogleg Drive Dormitory 8
    5 Edgewater Expressway Exit 2g
    6606 Fox Freeway
    7 Gateway Gorge Parkway

    AddressesBaseTable:
    (AddressesBaseTableID AddressNumber AddressName)
    1 101 Apple Avenue
    2 20 Bumblebee Boulevard
    3 33 Catbird Circle
    4 444 Dogleg Drive
    5 5 Edgewater Expressway
    6 6,606 Fox Freeway
    7 7 Gateway Gorge Parkway
    8 120 Bumblebee Boulevard
    9 90 Bumblebee Boulevard
    10 21 Bumblebee Boulevard

    AddressesSubUnitTable:
    (AddressesSubUnitTableID SubType SubUnitNumber)
    1 Apartment A63
    2 Building B4
    3 Condominium C6
    4 Dormitory 8
    5 Exit 2g

    Note:

    Running procedural methods against databases (with logical designs that are not normalized) that have many millions of rows of data may be expected to present serious performance issues.

    Using procedural methods to process small result sets as in the vkaramched example is a different matter. (For limited applications, or for migrating data to a normalized schemas, procedural approaches can be quite useful. If appropriate, it would probably be beneficial to create a generalized UDF to aid applying the necessary procedural methods).

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Re: Sensible ordering

    RE:
    ... If appropriate, it would probably be beneficial to create a generalized UDF to aid applying the necessary procedural methods).
    For an example, see dba_fn_ParseOutNonNumerics.sql
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2003
    Location
    Wiltshire, UK
    Posts
    5
    Thanks I'll try that out.

    Obviously my database will be better normalised than indicated in my example. My main problem was with ordering house numbers...

  6. #6
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Thanks I'll try that out. Obviously my database will be better normalised than indicated in my example. My main problem was with ordering house numbers...
    Indeed, you are welcome, and no offence intended.

    {There are some production systems that were built without the benefit of sound logical DB design practices; and others that may have been designed appropriately to earlier purposes that cannot appropriately meet newly arising requirements.}

    That said, here is a working demo of the normalised design, as previously described. (Allows a simple Select statement issued against the described view, with an appropriately constructed Order By clause, to automatically return the required result set.)

  7. #7
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Here is a working demo of the normalised design, as previously described. (Allows a simple Select statement issued against the described view, with an appropriately constructed Order By clause, to automatically return the required result set.)
    Attached Files Attached Files

Posting Permissions

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