MainelyDesign.com Blog

Distinct vs. Group By in MySQL (and CakePHP)

Posted on 05/21/2009 at 11:27 am by Kevin Wentworth
Viewed 21,745 times | 2 comments

An interesting problem- I am returning a list of files that have already been uploaded, so the user can link to an existing file.  This creates a new record (duplicate record).  The next time the user goes to select a file, there will now be two entries for the same file.  DISTINCT to the rescue, or so I thought.

DISTINCT applies to the whole select statement

This is confusing in its own right.  The CakePHP book misleadingly demonstrates that you can setup the fields parameter like so:

I tried this and a MySQL error kept getting returned.  I switched over to mysql and ran the query until it worked.  Turns out that the DISTINCT keyword has to come first in the SELECT statement, and it applies to the whole select statement (e.g. same category, but different name value makes it distinct).  I'm not sure really how to get CakePHP to output the correct query using the DISTINCT modifier- I think I would start the fields array() with DISTINCT... let me know if you figure it out.

GROUP BY applies only to the field(s) specified

The GROUP BY command takes a different approach and essentially "groups" all results by the fields you specify and returns a single instance of each unique value (what I wanted).  The code is simple:

This returned exactly what I needed.  Group By is the way to go, I think, in most cases. The only time DISTINCT is really good, is for returing all the distinct values of a single column.

To Recap

• DISTINCT applies to the entire SELECT statement (DISTINCT across all fields listed, not just 1 field if more than 1 are listed)
• GROUP BY applies to only the specified fields (a distinct value for the group by field only, doesn't factor in other fields being returned)
• For most cases use GROUP BY if you are returning more than 1 field and want distinct values for a particular column in the field list

Thanks to this simple example. Cheers,

-Kevin Wentworth