CODEWITHSUNDEEP
sqlselectdistinct
Magickarp
Magickarp

Reputation: 75

SQL DISTINCT in the middle of select

Does the distinct need to always be the first line after the select? Every time I try the following, I get a syntax error near the keyword 'DISTINCT, if not, how can I make the distinct work without begin the after the select.

SELECT COLUMN1,
DISTINCT COLUMN 2,
COLUMNM 3

FROM TABLE_1

it works if I have the distinct at in the first line like this:

SELECT DISTINCT COLUMN2,
COLUMN1,
COLUMN3

FROM TABLE_1

Upvotes: 3

Views: 10783

Answers (2)

jetpack_guy
jetpack_guy

Reputation: 360

Maybe I know where does this confusion comes from. While DISTINCT is indeed an optional SELECT predicate, which acts on each output row as a whole it can also be used inside function argument (which is also a statement on its own), e.g.:

SELECT s.id, s.name, GROUP_CONCAT(DISTINCT p.name SEPARATOR "; "), ...
FROM subjects AS s
LEFT JOIN property AS p ON p.subject_id = s.id
LEFT JOIN property2 AS p2 ON p2.subject_id = s.id
...
GROUP BY s.id;

The purpose of DISTINCT here is self-explanatory. But again, if we look at this inner statement DISTINCT comes first. Another example of same kind would be COUNT(DISTINCT column).

Upvotes: 0

The Impaler
The Impaler

Reputation: 48875

The DISTINCT clause filters out FULL DUPLICATE ROWS. It goes right after the SELECT keyword, since it applies to the entire row, not single columns. You cannot use it in between columns.

Upvotes: 5

Related Questions