SQLite Forum

documention: is DISTINCT optinoal in funtion?
Login

documention: is DISTINCT optinoal in funtion?

(1) By anonymous on 2021-06-12 02:48:11 [link] [source]

Hello,

I was reading the documentation for expr: https://www.sqlite.org/syntax/expr.html

When it comes to function call expression, it occurs to me in the document that the keyword DISTINCT isn't optional. But I tried it in the sqlite command line, it turns out to be optional (for example "select count(col-name) from table" is ok). I guess it's a problem in the document?

(2.2) By Keith Medcalf (kmedcalf) on 2021-06-12 04:15:50 edited from 2.1 in reply to 1 [source]

The DISTINCT or ALL keyword works exactly the same as it does everywhere else that it can be used. The default is ALL, except in the case of UNION where the default is DISTINCT.

SELECT ALL ....

means to return ALL the rows (even duplicate results) when performing the projection.

SELECT DISTINCT ...

means to return only the DISTINCT rows (duplicates removed) when performing the projection.

UNION ALL

means to return all the rows, even duplicates, when computing the resulting union of two projections

UNION DISTINCT means remove duplicate rows from the result when computing the union of two projections.

The default for UNION is DISTINCT and you may not specify the DISTINCT keyword.

The default for SELECT and aggregate functions is ALL and you may specify either keyword or none at all. An aggregate function in which the DISTINCT keyword is specified may only specify one argument.

(3) By Keith Medcalf (kmedcalf) on 2021-06-12 04:04:18 in reply to 2.0 [link] [source]

Example:

sqlite> select count(ALL value % 10) from wholenumber where value between 1 and 100;
┌───────────────────────┐
│ count(ALL value % 10) │
├───────────────────────┤
│ 100                   │
└───────────────────────┘
sqlite> select count(DISTINCT value % 10) from wholenumber where value between 1 and 100;
┌────────────────────────────┐
│ count(DISTINCT value % 10) │
├────────────────────────────┤
│ 10                         │
└────────────────────────────┘
sqlite>

(4) By Keith Medcalf (kmedcalf) on 2021-06-12 04:14:15 in reply to 2.1 [link] [source]

Note the railroad diagram is incorrect. You may specify:

function ( [ALL] arg, arg, arg, ... arg )
function ( DISTINCT arg )
function ( * )
function ( )

You cannot specify DISTINCT and more than one arg, although this does not apply to the optional ALL, which is silent.

(5) By anonymous on 2021-06-12 17:54:08 in reply to 1 [link] [source]

I looked, and the diagram is clearly incorrect. You should fix this please