Order by clause 1

[Zargo]

Hi all,

I have a table with the following content:

TABLEA
T1 | T2 | T3
123 1231243 GHAL
123 0003423 0001
123 3242342 0002
488 2342344 0001
456 3333444 0001
456 6526545 GHAL

I want to to order this table by T1 and T3, but the order of the T3 must be:

0001
0002
GHAL

When i try order by t1, t4 asc

Then i will get:

GHAL
0001
0002

And when doig order by t1, t4 desc, i'll get:

0002
0001
GHAL

How can i order this table to get the following order on t3:


0001
0002
GHAL

 

[Dagon]

Use a DECODE or CASE on the order by statement e.g.

decode(substr(t1,1,1), '0', 1, 2)

Might need to be more complex depending on exactly what your data is.

 

[Zargo]

Could you also please explain what this function exactly does(DECODE)? The t1 column can also contain characters like 'GE10023', does this function work ok?

 

[Dagon]

The DECODE checks the value of the first character of your column. If it is a zero, it uses the value '1'. Otherwise, it uses '2'. The net effect would be to sort all the strings beginning with a 0 first and all the others afterwards.

I don't know the full range of values in your data, which was why I was cautious in suggesting a solution and just gave an example. If you wanted to sort it so that fully numeric values come first and strings beginning with an alphabetic character come second, you could use something like:

case when
substr(t1,1,1) in
('0','1','2','3','4','5','6','7','8','9') then t1
else
'ZZZZ'||t1
end if;

However, I'm surprised you're getting a problem because alphabetic values are normally sorted after numeric values anyway, as shown by the following:

SELECT
'0001' as x
FROM DUAL
UNION
SELECT
'0002' as x
FROM DUAL
UNION
SELECT
'GHAL' as x
from dual
order by x;

I suppose it depends what character set your system uses, but it's certainly true with ASCII. Are you quite sure the values of T1 are the same for all these rows ?

 

[Zargo]

Hi,

There is a misinterpretation. In my case T4= T3

Couldn't we use

ORDER BY T1, decode(substr(t3,1,1), '0', 1, 2)

Because T3 contains always 0001, 0002, 0003 ... till 0010 and GHAL.

Will this issue solved with


ORDER BY T1, decode(substr(t3,1,1), '0', 1, 2)


TIA

 

[Beilstwh]

Actually, your order should be

ORDER BY T1, decode(substr(t3,1,1), '0', 1, 2),t3

Bill
Oracle DBA/Developer
New York State, USA