Hi,
I'm playing around with linguistic sorting and comparing and was using the examples from
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch5lingsort.htm#g1018324
So I have the three entries Große, große and Grosse in my table (the sample data from the manual)
To test these features, I did
alter session set nls_comp = Linguistic;
alter session set nls_sort = XGerman_CI;
and ran the following select statement:
SELECT name
FROM words
ORDER BY 1;
The rows are then returned in the following order:
Große
große
Grosse
which is exactly what I expect.
When I run
SELECT name
FROM words
WHERE name = 'Grosse';
it returns all three rows which is expected as well.
When I add the ORDER BY (which is the same ORDER BY as in the first step):
SELECT name
FROM words
WHERE name = 'Grosse'
ORDER BY 1;
the rows are returned in the following order
Große
Grosse
große
which is different to the order without the WHERE clause (and wrong)
The question is: why does the WHERE clause affect the ordering?