Sorting Problem
A poster wanted IDs such as E9333 and E10691 sorted numerically after the leading letter. Obnoxio The Clown suggested sorting on id[2,N] + 0 if the letter could be dropped. bozon proposed selecting id[1,1] and id[2,N] + 0 ordered descending. Art S. Kagel suggested ordering by id[1,1] then (id[2,6] + 0). The original poster did not respond.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi all, I have a sorting problem. Pls see below sql: SQL: select id from ptable order by id desc Result: id ------ E9333 E9266 E9258 E10691 E10660 Actually, I would like my result to be sorted like that: id ------ E10691 E10660 E9333 E9266 E9258 Which sql query should I use? Thanks for your help.
ggk517@gmail.com said: > Hi all, > > I have a sorting problem. Pls see below sql: > > SQL: select id from ptable order by id desc > > Result: > > id > ------ > E9333 > E9266 > E9258 > E10691 > E10660 > > Actually, I would like my result to be sorted like that: > > id > ------ > E10691 > E10660 > E9333 > E9266 > E9258 > > Which sql query should I use? > Thanks for your help. Interesting data. If you could lose the char in front, you could say Select id[2, N] + 0 id from table order by 1 desc But if the char in front varies or is important, then it's beyond me at half past seven in the morning. -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile
Select Select id[1,1], id[2, N] + 0 id from table order by 1 desc, 2 desc I would think. Luckily I waited till 7:50 my time to look at it. Obnoxio The Clown wrote: > ggk517@gmail.com said: > > Hi all, > > > > I have a sorting problem. Pls see below sql: > > > > SQL: select id from ptable order by id desc > > > > Result: > > > > id > > ------ > > E9333 > > E9266 > > E9258 > > E10691 > > E10660 > > > > Actually, I would like my result to be sorted like that: > > > > id > > ------ > > E10691 > > E10660 > > E9333 > > E9266 > > E9258 > > > > Which sql query should I use? > > Thanks for your help. > > Interesting data. If you could lose the char in front, you could say > > Select id[2, N] + 0 id from table order by 1 desc > > But if the char in front varies or is important, then it's beyond me at > half past seven in the morning. > > -- > Bye now, > Obnoxio > > "... no bill is required as no value was provided." > -- Christine Normile
ggk517@gmail.com wrote:
> Hi all,
>
> I have a sorting problem. Pls see below sql:
>
> SQL: select id from ptable order by id desc
>
> Result:
>
> id
> ------
> E9333
> E9266
> E9258
> E10691
> E10660
>
> Actually, I would like my result to be sorted like that:
>
> id
> ------
> E10691
> E10660
> E9333
> E9266
> E9258
>
> Which sql query should I use?
> Thanks for your help.
Not easy. Try this:
select id, id[1,1], (id[2,6] + 0)
from ...
order by 2, 3;
Art S. Kagel