Ordering on Hierarchical Dot Notation

From: dar7yl (no_reply_at_accepted.org)
Date: 01/20/05 

Date: Thu, 20 Jan 2005 20:24:28 GMT

I have a SQL database (MySql) containing a table with a key column which
uses "Hierarchical .Dot Notation".
vis:
    1
    1.1
    1.2
    2
    2.1
    2.2
    2.10
    3
    10
    10.1
    11
    ...

The problem is that this column is proving very difficult to sort naturally
after the values get up to 10. They sort like this:
    1
    1.1
    1.2
    10
    10.1
    11
    2
    2.1
    2.10
    2.2
    3
...

I want to solve this without having to change the original column. The
users still want to query the database using the natural representation.

Basically, I can see of three methods:

1) Provide an auxillary column, and when adding records, set up this field
coded to sort in the desired order. For instance, extract each level as an
integer and append it to a variable byte array (stored as a BLOB). Then
include "ORDER BY Aux" in the query.

2) Query the database unordered, and sort the ResultSet myself.

3) Code a function module in MySql which can compare two strings in
hierarchical format, and use that function in the query.

Option 1 involves up-front application changes, expecially when adding data,
and would have to be performed for each existing and future application.
Non-controlled applications (for instance a stand-alone sql query, or a
report generator) can use the table except for adding records. Storage
representation limits number of items per level (for instance 255 items for
a BYTE array).

Option 2 also involves up-front application changes and precludes using the
table in non-controlled applications.

Option 3 involves minimal application impact, but involves delving deep into
MySql's operation. Also, it's not portable across db's.

Any thoughts on this would be appreciated.

regards,
    Dar7yl

Relevant Pages

  • Ordering on Hierarchical Dot Notation
    ... I have a SQL database (MySql) containing a table with a key column which ... The problem is that this column is proving very difficult to sort naturally ... users still want to query the database using the natural representation. ...
    (comp.lang.java.databases)
  • Re: sorting
    ... When I sort the data using SQL, ... But this is just the sorting. ... If it were me I would query the database and bring back the ...
    (microsoft.public.vb.enterprise)
  • Re: [PHP] Problem with SELECT statement and reference material wanted..
    ... echo you query and then run it on mysql and see u getting the desired ... I'm having trouble retrieving a selection of my database contents. ... ..I will get all entries from the database, ... required to be named as the column names in the MySQL table, ...
    (php.general)
  • Re: Ordering on Hierarchical Dot Notation
    ... > I have a SQL database (MySql) containing a table with a key column which ... They sort like this: ... > users still want to query the database using the natural representation. ...
    (comp.lang.java.programmer)
  • Re: Ordering on Hierarchical Dot Notation
    ... > I have a SQL database (MySql) containing a table with a key column which ... They sort like this: ... > users still want to query the database using the natural representation. ...
    (comp.lang.java.databases)