[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