CODEWITHSUNDEEP
mysqlsqldatabaseselect
PeakGen
PeakGen

Reputation: 22995

Error when `DISTINCT` keyword is used?

Please have a look at the below query

SELECT Client_Portfolio.*,
DISTINCT Client.Name AS "Client Name", 
Portfolio.Portfolio_Type 
FROM Client_Portfolio 
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient 
INNER JOIN Portfolio ON Portfolio.idPortfolio = Client_Portfolio.idPortfolio 
WHERE Portfolio.Portfolio_Type = "Out"

I wanted to remove the records which duplicates the "Client Name" so I used the keyword distinct. But it gives the below error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct (Client.Name) AS "Client Name",
Portfolio.Portfolio_Type
FROM Client_' at line 2

What have I done wrong?

Upvotes: 0

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The syntax for select distinct is exactly that, select distinct:

SELECT DISTINCT cp.*, c.Name AS "Client Name", p.Portfolio_Type 
FROM Client_Portfolio cp INNER JOIN
     Client cp
     ON c.idClient = cp.idClient INNER JOIN
     Portfolio
     ON p.idPortfolio = cp.idPortfolio 
WHERE p.Portfolio_Type = 'Out';

It applies to all columns in the select list.

I don't know if this is what you want, but it is the correct syntax.

Note I made two other changes to your query. First, I added table aliases to make the query easier to write and to read. Second, I put the string constant in single quotes rather than double quotes.

Upvotes: 2

Related Questions