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?
Advertisement
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