Link to home
Start Free TrialLog in
Avatar of Pete Winter
Pete WinterFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Query

See my code below...

$query_rs_media_new_group = "SELECT title, group_id, description, brand, weight, category, width, min(id) as id FROM media_new group by title ORDER BY id";

Open in new window


I tried adding
 WHERE type = 'Media' 

Open in new window

so it appears as

$query_rs_media_new_group = "SELECT title, group_id, description, brand, weight, category, width, min(id) as id FROM media_new group  WHERE type = 'Media'  by title ORDER BY id";

Open in new window


I got this error:

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 'WHERE type = 'Media' by title ORDER BY id' at line 1

What have I done wrong and how do I resolve?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Keep Group By together (you put  the where clause between group and by):
$query_rs_media_new_group = "SELECT title, group_id, description, brand, weight, category, width, min(id) as id FROM media_new  WHERE type = 'Media' group by title ORDER BY id";
Avatar of Anwar Saiah
Anwar Saiah

'Media' by title ORDER BY id' at line 1
should be
 'Media' ORDER by title
Avatar of Pete Winter

ASKER

Many thanks
Some of this can be made easier if you adopt a coding standard that requires you to capitalize the MYSQL reserved words and organize the query string so its clauses are written on different lines.  

Instead of this:
$query_rs_media_new_group = "SELECT title, group_id, description, brand, weight, category, width, min(id) as id FROM media_new group by title ORDER BY id";

Open in new window

More like this:
$query_rs_media_new_group = 
"SELECT title, group_id, description, brand, weight, category, width, MIN(id) AS id 
FROM media_new 
GROUP BY title 
ORDER BY id"
;

Open in new window

With a final objective of something like this:
$query_rs_media_new_group = 
"SELECT title, group_id, description, brand, weight, category, width, MIN(id) AS id 
FROM media_new 
WHERE type='Media'
GROUP BY title 
ORDER BY id"
;

Open in new window

I'm so glad a SQL Server answer was provided here.

MySQL (the database) is SO SLOPPY with grouping and yet it seems to be utterly ignored.

Jim is completely correct: EVERY non-aggregating column should be included in the GROUP BY clause. MySQL can allow you (depending on server settings) to ignore this BUT:
a. it then uses "approximations" to produce the groupings, and
b. because server settings can be altered a sloppy abbreviated group by clause can fail

So, the message is, don't be sloppy with the group by clause.

& then also, agreeing with Ray's excellent suggestion regarding how to use separate rows for the SQL we end up with a query that looks like this:
$query_rs_media_new_group = 
"SELECT title, group_id, description, brand, weight, category, width, MIN(id) AS id 
FROM media_new 
WHERE type='Media'
GROUP BY title, group_id, description, brand, weight, category, width 
ORDER BY id"
;

Open in new window

plus: It would be more conventional to order by a column include in the select clause (probably title)