Re: order by clause
Date: 25 Sep 1993 11:54:50 GMT
Message-ID: <OLLI.RINNE.93Sep25145449_at_titi-uu.tik.vtt.fi>
ad041_at_Freenet.carleton.ca (Allison Rothmel) writes:
> I believe this should be a simple command.
> I want to ORDER BY fieldname but if the field names are xxx-11, xxx-12,
> xxx-13, xxx-112 and xxx-113 I want them to appear in that order not 11,
> 112, 113, 12, 13 which appears by default.
> It only gets tricky because there are other fields like yyyy 712
> and zzzz 516,
I think you should separate the first part and the number part.
Here's two solutions. Not very elegant or fast but working. First one works only if you are really sure that the first characters are not numbers and the last characters will be numbers.
SELECT
name
,RTRIM(name,'1234567890')
,SUBSTR(name,LENGTH(RTRIM(name,'1234567890'))+1)
FROM test
ORDER BY
RTRIM(name,'1234567890')
,TO_NUMBER(SUBSTR(name,LENGTH(RTRIM(name,'1234567890'))+1))
;
Result will be something like this:
xxx-11 xxx- 11 xxx-12 xxx- 12 xxx-112 xxx- 112 xxx-113 xxx- 113 yyyy 71 yyyy 71 yyyy 712 yyyy 712 zzzz 516 zzzz 516 zzzz 712 zzzz 712
Another possibility - if you know sure that there is separator (space or - ) and the last part is number.
SELECT
name
,SUBSTR(name,1,INSTR(TRANSLATE(name,' -',' '),' ')-1)
,SUBSTR(name,INSTR(TRANSLATE(name,' -',' '),' ')+1)
FROM test
ORDER BY
SUBSTR(name,1,INSTR(TRANSLATE(name,' -',' '),' ')-1)
,TO_NUMBER(SUBSTR(name,INSTR(TRANSLATE(name,' -',' '),' ')+1))
;
And the result is:
xxx-11 xxx 11 xxx-12 xxx 12 xxx-112 xxx 112 xxx-113 xxx 113 yyyy 71 yyyy 71 yyyy 712 yyyy 712 zzzz 516 zzzz 516 zzzz 712 zzzz 712
Of course, you don't have to have those ordering fields in SELECT-clause only in ORDER BY.
olli R Received on Sat Sep 25 1993 - 13:54:50 CEST