Min item is not the first when ordered by

Question

Here is a basic example:

CREATE TABLE samuel(id varchar(1), field varchar(1));
INSERT INTO samuel VALUES ('1', ' ');
INSERT INTO samuel VALUES ('2', '&');

When doing SELECT id, field, DUMP(field) FROM samuel order by field; with Oracle 11.2.0.4.0, I get:

ID	FIELD	DUMP
2	&	Typ=1 Len=1: 38
1		Typ=1 Len=1: 32

But when doing select id from samuel where field=(select min(field) from samuel); I get 1!

Here are the NLS settings:

PARAMETER	VALUE
NLS_LANGUAGE	FRENCH
NLS_TERRITORY	FRANCE
NLS_CURRENCY	€
NLS_ISO_CURRENCY	FRANCE
NLS_NUMERIC_CHARACTERS	,
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD/MM/YYYY HH24:MI:SS
NLS_DATE_LANGUAGE	FRENCH
NLS_SORT	FRENCH
NLS_TIME_FORMAT	HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	DD/MM/YYYY HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT	HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT	DD/MM/YYYY HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY	€
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

Why is that?

Answer 1

Your profile says you are in France.

Given the setup:

ALTER SESSION SET NLS_LANGUAGE = 'French';
CREATE TABLE samuel(id varchar(1), field varchar(1));
INSERT INTO samuel VALUES ('1', ' ');
INSERT INTO samuel VALUES ('2', '&');

Then the default NLS settings for that language:

SELECT * FROM NLS_SESSION_PARAMETERS;

Include:

PARAMETER	VALUE
NLS_LANGUAGE	FRENCH
NLS_SORT	FRENCH
NLS_COMP	BINARY

Then:

SELECT id, field FROM samuel order by field;

Outputs:

ID	FIELD
2	&
1

If you force the sort order to be binary:

SELECT id, field FROM samuel order by NLSSORT(field, 'NLS_SORT=BINARY');

Then the output is:

ID	FIELD
1
2	&

You need to make sure that you are sorting based on the rules you require. If necessary, you can use:

ALTER SESSION SET NLS_SORT='BINARY';

(However, that would then use binary sorting for all queries in that session; not just this one query.)

Then:

SELECT id, field FROM samuel order by field;

Outputs:

ID	FIELD
1
2	&

fiddle