[thelist] SQL Order By Clause

rudy r937 at interlog.com
Wed Jul 25 19:06:00 CDT 2001


>I would like to order the Item No. column in numerical order, such as
>1,2,3,4 instead of the order it is in now, 1, 10, 100, etc.  This field is
>text (see the end, text entries).


hi michele

two solutions i can think of, off the top of my head

1. do some sort of conversion (and how tricky this is depends on the
database you're using; at worst you would substring the item number,
discarding non-numeric characters, from right to left, etc.) while
rebuilding your table so that you eliminate non-numeric item numbers

not a good solution especially if the products already have stickers on
them  (superior elitist data architect snide remark:  DBAs never think of
that kind of detail, do they?)

2. have a companion field alongside Item No, defined numeric, in which you
store a sequence field -- this offers maximum flexibility, as you could
then sequence them any which way you like, including

    100 Snowman Dish (Glazed Inside)
    304 Frosty Snowman
  F203 Birdhouse Snowman
  F205 Papa Snowman
  F206 Mama Snowman
  F207 Baby Snowman
  F208 Santa Hugging Snowman
      45 Hand In Hand Snowman Double Sided
    308 Oak Knob Snowman
    536 Flat Snowman Ornaments (Set of 2)

in other words, grouped, rather than in numerical sequence


of course, if you went with method 2, you would use method 1 to populate
the sequence numbers initially...


what database are you using, microsoft access?  if so, there's a neat way
to do the conversion

i'd make this a tip, but the preamble would take too long to write...


add the new column, let's call it Seq, using Required=No (i.e. allow nulls)

then run

        update myTable
           set Seq = ItemNo

you will get a conversion error for every non-numeric Item No, but that's
okay

then run

     select ItemNo, Seq, '        ' from myTable
       where Seq is NOT null
   union all
     select ItemNo, Seq, '**oops**' from myTable
       where Seq IS null
    order by 1

that'll give you your original table in your original sequence, and flag
all the items which had a non-numeric ItemNo

in fact, if you run just the second query in that UNION, it becomes
updateable, so you can actually update the missing values right there in
the query results!


gotta love access as a desktop database


rudy





More information about the thelist mailing list