Distinct () function (not select selectifier) ​​in postgres

Question

I just ran into an SQL query, specifically a Postgres database that uses a function called "different". Namely:

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

Please note that this is NOT a regular DISTINCT separator for SELECT - at least this is not a regular syntax for the DISTINCT classifier, pay attention to parentheses. Apparently, it uses the DISTINCT function as a function, or maybe this is a special syntax.

Any idea what that means?

I tried playing with him a little bit and if I write

select distinct(foo)
from bar

I get the same results as

select distinct foo
from bar

When I combine it with other fields in the same select, it is not clear to me what exactly it does.

I can not find anything in the Postgres documentation.

Thanks for any help!

Answer 1

^{( , Google : "sql distinct " (-, Stack Overflow), , ...)}

DISTINCT SELECT, ( ).

DISTINCT , . (), ,

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

:

_{- distinct:}

select distinct (pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

_{- :}

select distinct pattern as pattern, style, ... etc ...
from styleview
where ... etc ...

_{- :}

select distinct
    pattern as pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

_{- , :}

select distinct
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

:

• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
• fooobar.com/questions/1051284/...

---

: OMG Ponies DISTINCT ON, PostgreSQL.
( ) , , ( ) , :

select distinct on(pattern) pattern, style, ... etc ...
from styleview
where ... etc ...
order by pattern, ... etc ...

:

select  distinct on (pattern)
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...
order by
    pattern, ... etc ...

:

• http://www.noelherrick.com/blog/postgres-distinct-on

---

: DISTINCT :
COUNT(DISTINCT (foo, bar, ...)), HSQLDB
( COUNT(DISTINCT foo, bar, ...), MySQL, PostgreSQL, SQL Server, Oracle , , ).
( ) , .

Answer 2

:

DISTINCT, ( ). ALL : ; .

DISTINCT ON ( [,...]) , . DISTINCT ON , ORDER BY (. ). , " " , ORDER BY , , . ,

ON , :

• - SQL Server MySQL , DISTINCT , SELECT

PostgreSQL - , , , .

Answer 3

, - , .

, ( ). , , , .

, :

select foo
from bar

select (foo)
from bar

, , :

select (foo, baz)
from bar

, , , :

select distinct *
from
(
    select pattern as pattern, style, ... etc ...
    from styleview
    where ... etc ...
)

, . , , DISTINCT ON (...), .

Answer 4

PostgreSQL:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]

, ON , , ON, . , ON, .

, [ON ( [,...])] .

:

CREATE TABLE bar
    (foo varchar(3), fub varchar(1), flut timestamp)
;

INSERT INTO bar
    (foo, fub, flut)
VALUES
    ('one', 'a', '2016-01-01 01:01:03'),
    ('one', 'b', '2016-01-01 01:01:02'),
    ('one', 'c', '2016-01-01 01:01:01'),
    ('two', 'd', '2016-01-01 01:01:03'),
    ('two', 'e', '2016-01-01 01:01:02'),
    ('two', 'f', '2016-01-01 01:01:01')
;

. ? .

select (foo) from bar;

| foo |
|-----|
| one |
| one |
| one |
| two |
| two |
| two |

, , , foo, , , , . . , DISTINCT?

select distinct(foo) from bar;

| foo |
|-----|
| two |
| one |

select distinct foo from bar;

| foo |
|-----|
| two |
| one |

, , . , . DISTINCT , DISTINCT .

, :

SQL-, , Postgres, "different". :

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

DISTINCT ! .

---

---

[ON ()] .

:

select distinct ON (foo) foo, fub, flut from bar order by foo

| foo | fub |                      flut |
|-----|-----|---------------------------|
| one |   a | January, 01 2016 01:01:03 |
| two |   d | January, 01 2016 01:01:03 |

b:

select distinct ON (fub) foo, fub, flut from bar order by fub

| foo | fub |                      flut |
|-----|-----|---------------------------|
| one |   a | January, 01 2016 01:01:03 |
| one |   b | January, 01 2016 01:01:02 |
| one |   c | January, 01 2016 01:01:01 |
| two |   d | January, 01 2016 01:01:03 |
| two |   e | January, 01 2016 01:01:02 |
| two |   f | January, 01 2016 01:01:01 |

c:

select distinct ON (flut) foo, fub, flut from bar order by flut

| foo | fub |                      flut |
|-----|-----|---------------------------|
| one |   c | January, 01 2016 01:01:01 |
| one |   b | January, 01 2016 01:01:02 |
| one |   a | January, 01 2016 01:01:03 |

[ON ()] , "" "" " " " " , , ORDER BY, , order by ALSO , SELECT DISTINCT ON PostgreSQL, :

: SELECT DISTINCT ON ORDER BY

sqlfiddle

---

While I do not want to complicate my answer, it is worth mentioning the wrinkle:

select distinct (foo,fub) from bar;

Now the brackets do something , but what they do is not directly related to the individual. See " complex types "