Question about sorting

[greaseman]

[RESOLVED] Question about sorting

I have a table with an alphanumeric field I want to sort on. If I sort the usual ascending way, data that is numeric is sorted before data that is alpha.

Is there a way to have the alpha data sorted ascending come out ahead of the numeric data srted ascending?

Ex:

Currently,
1
2
3
A
B
c

Desired:

A
B
C
1
2
3


Thanks in advance to all who reply!!!

 

[FoFa]

Create two queries, one that select all the letters and one that selects all the numbers. Union them together.
SELECT Col1, Col2, Col3, Col4, "A" as SortFlag From MyTable where Col1 Between "a" and "Z"
UNION
SELECT Col1, Col2, Col3, Col4, "B" as SortFlag From MyTable where Col1 Between "0" and "9"
Order by SortFlag, Col1

 

[greaseman]

FoFa,

Thanks for your reply..... I'll give that a try, although I'm not familiar at all with UNION queries. It sounds like a good bet, though.

Appreciate your quickness in responding

 

[greaseman]

FoFa,

It dawned on me that I could use my original query, simply by adding one extra field, as in your UNION suggestion: If the first character of my desired field was a letter, my new field would contain an "A", and if the first character was a number, my new field would then contain a "B.". Next, I sorted on this new field and then sorted on my original fields.

That worked..... I didn't need a UNION query at all. Sometimes, it just ends up being a case of not seeing the forest for the trees.

In any case, I still appreciate your suggestion. Have a good day!