Re: Select query with GROUP condition thens order by alphanumeric

[Chu Soon Tan]

Mark Snyder Wrote:
-------------------------------------------------------
> SELECT...
> GROUP BY...
> ORDER BY...
>
> works fine.
>
> What have you tried? And what does the output look
> like?


Dear Mark:

yes i tried. But still can get the order i wanted.

For example:

here the data:

-----------
1asds
a23
a-12
abs
b2ww3asd
bbdhas
v21
8bd
b-12
812
a23
s34123
8bd
0123
8-3
-----------


if we use group by then order by, the our will follow numerical them alpha.....


then i tried order char by char, it was not the order of output i wated:

ORDER BY CASE WHEN SUBSTRING(YourColumn,1,1) BETWEEN '0' AND '9' THEN 1 ELSE 0 END, YourColumn


isn't possible to order the each string char by char in this order (special symbol (-, * and etc), then alphabet, then numeric.)


this the order of output i try to get:
---------------
a-12
abs
a23
b-12
bbdhas
b2ww3asd
s34123
v21
0123
1asds
8-3
8bd
812
---------------