CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Aminos
Aminos

Reputation: 103

Oracle order by unexpected result

I have this query : 

SELECT DISTINCT 
ID_USER, 
NUMERO, -- VARCHAR2(8 BYTE) 
NOM
FROM USER_VIEW 
WHERE UPPER(NOM) = 'JACKSON'
ORDER BY NUMERO ASC

I expect results to be like this : 

ID_USER  |   NUMERO   |   NOM
--------------------------------
1        |   TI33     |  JACKSON
9        |   TI99     |  JACKSON
4        |   1999     |  JACKSON
0        |   2001     |  JACKSON
3        |   2006     |  JACKSON
8        |   *04      |  JACKSON
5        |   *15      |  JACKSON
7        |   *61      |  JACKSON

but I'm getting unwanted results :

ID_USER  |   NUMERO   |   NOM
--------------------------------
1        |   TI33     |  JACKSON
9        |   TI99     |  JACKSON
8        |   *04      |  JACKSON
5        |   *15      |  JACKSON
4        |   1999     |  JACKSON
0        |   2001     |  JACKSON
3        |   2006     |  JACKSON
7        |   *61      |  JACKSON

Can somebody explain why I'm getting these results ? and how can I fix this?

Upvotes: 0

Views: 89

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

You seem to be using linguistic sorting and matching, and whatever NLS_SORT is set to in your session - or possibly a column-level collation setting - is causing you to encounter ignorable characters.

With my default session I don't see the same as you:

alter session set nls_sort = binary;
alter session set nls_comp = binary;

with user_view (id_user, numero, nom) as (
  select 1, cast('TI33' as varchar2(8 byte)), 'JACKSON' from dual
  union all select 9, 'TI99', 'JACKSON' from dual
  union all select 4, '1999', 'JACKSON' from dual
  union all select 0, '2001', 'JACKSON' from dual
  union all select 3, '2006', 'JACKSON' from dual
  union all select 8, '*04', 'JACKSON' from dual
  union all select 5, '*15', 'JACKSON' from dual
  union all select 7, '*61', 'JACKSON' from dual
)
SELECT DISTINCT 
ID_USER, 
NUMERO,
NOM
FROM USER_VIEW 
WHERE UPPER(NOM) = 'JACKSON'
ORDER BY NUMERO ASC
/

   ID_USER NUMERO   NOM    
---------- -------- -------
         8 *04      JACKSON
         5 *15      JACKSON
         7 *61      JACKSON
         4 1999     JACKSON
         0 2001     JACKSON
         3 2006     JACKSON
         1 TI33     JACKSON
         9 TI99     JACKSON

If I change the settings (picking a language at random) then I do:

alter session set nls_sort = spanish_ci;
alter session set nls_comp = linguistic;

...

   ID_USER NUMERO   NOM    
---------- -------- -------
         1 TI33     JACKSON
         9 TI99     JACKSON
         8 *04      JACKSON
         5 *15      JACKSON
         4 1999     JACKSON
         0 2001     JACKSON
         3 2006     JACKSON
         7 *61      JACKSON

You can change the session, or override the sort for just that column using the nlssort() function:

SELECT DISTINCT 
ID_USER, 
NUMERO,
NOM
FROM USER_VIEW 
WHERE UPPER(NOM) = 'JACKSON'
ORDER BY nlssort(NUMERO, 'NLS_SORT=BINARY') ASC
/

   ID_USER NUMERO   NOM    
---------- -------- -------
         8 *04      JACKSON
         5 *15      JACKSON
         7 *61      JACKSON
         4 1999     JACKSON
         0 2001     JACKSON
         3 2006     JACKSON
         1 TI33     JACKSON
         9 TI99     JACKSON

but that still puts the * values first.

You might have to use a case expression to fix that:

SELECT DISTINCT 
ID_USER, 
NUMERO,
NOM
FROM USER_VIEW 
WHERE UPPER(NOM) = 'JACKSON'
ORDER BY CASE WHEN SUBSTR(NUMERO, 1, 1) = '*' then 2 else 1 end, NUMERO
/

   ID_USER NUMERO   NOM    
---------- -------- -------
         1 TI33     JACKSON
         9 TI99     JACKSON
         4 1999     JACKSON
         0 2001     JACKSON
         3 2006     JACKSON
         8 *04      JACKSON
         5 *15      JACKSON
         7 *61      JACKSON

I have a framework that append ASC or DESC to the custom query depending on a boutton in a GUI and I can't touch the framework to make it change values in the case expression also

Then you can concatenate the case expression result (as string rather than numbers, with any characters that are in the right order in your collation really); as you order by a single combined expression you can order that ascending:

SELECT DISTINCT 
ID_USER, 
NUMERO,
NOM
FROM USER_VIEW 
WHERE UPPER(NOM) = 'JACKSON'
ORDER BY CASE WHEN SUBSTR(NUMERO, 1, 1) = '*' then 'B' else 'A' end || NUMERO ASC
/

   ID_USER NUMERO   NOM    
---------- -------- -------
         1 TI33     JACKSON
         9 TI99     JACKSON
         4 1999     JACKSON
         0 2001     JACKSON
         3 2006     JACKSON
         8 *04      JACKSON
         5 *15      JACKSON
         7 *61      JACKSON

... or descending:

SELECT DISTINCT 
ID_USER, 
NUMERO,
NOM
FROM USER_VIEW 
WHERE UPPER(NOM) = 'JACKSON'
ORDER BY CASE WHEN SUBSTR(NUMERO, 1, 1) = '*' then 'B' else 'A' end || NUMERO DESC
/

   ID_USER NUMERO   NOM    
---------- -------- -------
         7 *61      JACKSON
         5 *15      JACKSON
         8 *04      JACKSON
         3 2006     JACKSON
         0 2001     JACKSON
         4 1999     JACKSON
         9 TI99     JACKSON
         1 TI33     JACKSON

Upvotes: 2

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

You can use:

select ID_USER, NUMERO, NOM 
from user_view
WHERE UPPER(NOM) = 'JACKSON'
order by replace(numero,'*','')

Upvotes: 0

Related Questions