Sort alphanumeric ID number

I have a database that every transactions get a alphanumeric ID. By when I write my query and use Order by it give me a wrong sequence.

For example:

TRN_ID      TRNDTE
000002DAAW  2020-09-12 03:45:24
000002DAAX  2020-09-12 03:45:32
000002DAAY  2020-09-12 03:45:34
000002DAAZ  2020-09-12 03:45:38
000002DAA0  2020-09-12 03:35:16
000002DAA1  2020-09-12 03:35:25
000002DAA2  2020-09-12 03:35:26
000002DAA3  2020-09-12 03:35:30
000002DAA4  2020-09-12 03:35:39
000002DAA5  2020-09-12 03:35:40
000002DAA6  2020-09-12 03:35:44
000002DAA7  2020-09-12 03:36:00
000002DAA8  2020-09-12 03:36:01
000002DAA9  2020-09-12 03:36:05
000002DABA  2020-09-12 03:48:12 <-
000002DABB  2020-09-12 03:48:15
000002DABC  2020-09-12 03:48:32
000002DABD  2020-09-12 03:48:33
000002DABE  2020-09-12 03:48:36
000002DABF  2020-09-12 03:48:46
000002DABG  2020-09-12 03:48:47
000002DABH  2020-09-12 03:48:50
000002DABI  2020-09-12 03:49:06
000002DABJ  2020-09-12 03:49:06
000002DABK  2020-09-12 03:49:09
000002DABL  2020-09-12 03:49:19
000002DABM  2020-09-12 03:49:20
000002DABN  2020-09-12 03:49:24
000002DABO  2020-09-12 03:49:33
000002DABP  2020-09-12 03:49:34
000002DABQ  2020-09-12 03:49:37
000002DABR  2020-09-12 03:49:48
000002DABS  2020-09-12 03:49:48
000002DABT  2020-09-12 03:49:51
000002DABU  2020-09-12 03:50:01
000002DABV  2020-09-12 03:50:01
000002DABW  2020-09-12 03:50:05
000002DABX  2020-09-12 03:50:15
000002DABY  2020-09-12 03:50:15
000002DABZ  2020-09-12 03:50:18
000002DAB0  2020-09-12 03:46:23 <-
000002DAB1  2020-09-12 03:46:24
000002DAB2  2020-09-12 03:46:28
000002DAB3  2020-09-12 03:47:18
000002DAB4  2020-09-12 03:47:18

As you can see the query is ordering A to Z then 0 to 9, but it should order 0 to 9 then A to Z

There is a way set a string to order it correctly?

Answer

It is very likely that Trincot’s observation (second comment under your question) is correct: You see ascending ordering with letters before digits (instead of the opposite), because your collation requires it.

From your profile I assume you are in Brazil, and your NLS_LANGUAGE parameter is 'BRAZILIAN PORTUGUESE', with the default NLS_SORT parameter (derived from NLS_LANGUAGE) of 'WEST_EUROPEAN'. It’s not Oracle’s choice; Brazilian Portuguese collation (or, rather more accurately, West-European collation) has digits after letters, not before them. See a demo at the end of this answer.

So, this explains the problem. How can you fix it?

If you have a lot of similar queries, and you must show digits before letters in all of them (or if you have ORDER BY in many places in a query – for example in analytic functions, or in match_recognize, etc.), then it makes sense to change the NLS_SORT parameter for the session, with

alter session set nls_sort='BINARY';

If you only need to do it in a single query, in the ORDER BY clause, you can just change the sort order locally (for that ORDER BY clause only, without affecting anything else) by using the NLSSORT function. Like this:

.... order by nlssort(TRN_ID, 'NLS_SORT=BINARY')

Here is a brief demo.

First, on my system, NLS_LANGUAGE is 'AMERICAN', with the default NLS_SORT (for this language) of 'BINARY'. This is why letters come after digits:

select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;

COL
---
3
A

Now let me change my NLS_LANGUAGE to 'BRAZILIAN PORTUGUESE'. Then let’s see how this affected NLS_SORT (automatically), and what that does to the query:

alter session set nls_language='BRAZILIAN PORTUGUESE';

select parameter, value
from   v$nls_parameters
where  parameter in ('NLS_LANGUAGE', 'NLS_SORT');

PARAMETER    VALUE                 
------------ ----------------------
NLS_LANGUAGE BRAZILIAN PORTUGUESE  
NLS_SORT     WEST_EUROPEAN       



select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;

COL
---
A
3

Now, without changing the language, let me just change the collating sequence (NLS_SORT parameter) to 'BINARY' – and see how the query produces the desired result. We didn’t change the language, but we changed the collating sequence for the entire session. This will work for all other queries in the current session.

alter session set nls_sort='BINARY';

select parameter, value
from   v$nls_parameters
where  parameter in ('NLS_LANGUAGE', 'NLS_SORT');

PARAMETER    VALUE                 
------------ ----------------------
NLS_LANGUAGE BRAZILIAN PORTUGUESE  
NLS_SORT     BINARY 




select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;

COL
---
3
A   

Finally, let me change NLS_SORT back to 'WEST_EUROPEAN' and use the NLSSORT function in the ORDER BY clause of the query to get the same result (the one you need) without touching the session parameters.

alter session set nls_sort='WEST_EUROPEAN';

select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by col;   -- This will produce the wrong order! See below for fix.

COL
---
A
3  

select col
from   (select 'A' as col from dual union all select '3' from dual)
order  by nlssort(col, 'nls_sort=BINARY');

COL
---
3
A