ORDER in UNION ALL don't works

Question

first of all i'm sorry for my english.

I've two tables with same columns and i want to join both in SELECT and order by 'nombre'. My problem is that it don't work as I expect. For example:

Table 1 (ListaAmbos):

ID  nombre
1   Asaber
2   Zip

Table 2 (Listax86):

ID  nombre
24  rtywr
25  afaf
26  stftst'gdh
27  dgsdhttrh%%
28  Prueba

What i want:

ID  nombre
25  afaf
1   Asaber
27  dgsdhttrh%%
28  Prueba
24  rtywr
26  stftst'gdh
2   Zip

What i get:

ID  nombre
1   Asaber
28  Prueba
2   Zip
25  afaf
27  dgsdhttrh%%
24  rtywr<br>
26  stftst'gdh

Im using this query:

SELECT ID, nombre FROM ListaAmbos 
UNION ALL 
SELECT ID, nombre FROM Listax86 
ORDER BY nombre ASC;

but don't work like i want... What im doing wrong?

Thanks in advance.

Answer 1

What you're getting is upper case first, then lower case, which means you're using a case-sensitive collation. Change your query to:

...
ORDER BY nombre COLLATE NOCASE ASC

See also: How to use SQL Order By statement to sort results case insensitive?

Answer 2

How about trying like this:-

 SELECT ID, nombre FROM ListaAmbos
 UNION ALL SELECT ID, nombre FROM Listax86 ORDER BY nombre COLLATE NOCASE ASC;