Re: order by clause

From: olli R <Olli.Rinne_at_tik.vtt.fi>
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

Original text of this message