- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
- (06/12) View Layer - Getting count of Entities in View in Cakephp 3TAGS:CakephpCake3
- (05/23) Changing URL in beforeRedirect() in Component - Cakephp 3TAGS:CakephpCake3Web Programming
- (01/28) Foundation CSS Framework ResourcesTAGS:FoundationCss
- (01/27) Change View File from Controller in CakePHP 3TAGS:CakephpCake3UsageWeb Programming
- (01/13) Loading Model/Table Anywhere in Cake3TAGS:Cake3Cakephp
- (10/06) CURL PHP 5.5 Issue on Windows (Using Twilio SDK)
Subscribe to my feed
Distinct vs. Group By in MySQL (and CakePHP)
Posted on 05/21/2009 at 11:27 am by Kevin Wentworth
Viewed 14,967 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.
- 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,