MySQL DISTINCT function not working

Question

Using MySQL. The following SQL statement works perfectly:

SELECT a.ID, a.DISPNAME, a.UNIT_TYPE, a.MODELNUM, a.DESCRIP, a.DATE_ADDED, b.TYP, c.FIRSTNAME, c.LASTNAME FROM jobs_engineering a INNER JOIN unit_types b ON a.UNIT_TYPE=b.ID INNER JOIN users c ON a.AEID=c.USERID ORDER BY a.DATE_ADDED DESC

But now I want only unique records to the column DISPNAME as that column has multiple repeated values. I add the DISTINCT method

SELECT a.ID, DISTINCT(a.DISPNAME), a.UNIT_TYPE, a.MODELNUM, a.DESCRIP, a.DATE_ADDED, b.TYP, c.FIRSTNAME, c.LASTNAME FROM jobs_engineering a INNER JOIN unit_types b ON a.UNIT_TYPE=b.ID INNER JOIN users c ON a.AEID=c.USERID ORDER BY a.DATE_ADDED DESC

But that does not work. MySQL tells me my error is near "DISTINCT"

I've tried:

 a.DISTINCT(DISPNAME)
 DISTINCT(DISPNAME)

None of those work

Where is my error? Can anyone help?

Answer 1

DISTINCT is not a function, but a query decorator. This means, you use SELECT DISTINCT instead of SELECT if you want the query to supress all duplicates.

Now the important part is, a row is a duplicate of another row, if (and only if) all columns have the same value.

What you want to achieve is typically solved either

• with a GROUP BY on the relevant column and fitting aggregate functions
• with a subquery that has a LIMIT 1