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?
petewinterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not sure about MySQL-specific, but the SQL Server answer is that your query is missing a GROUP BY clause that contains all column names except for what's being aggregated (Min).
SELECT title, group_id, description, brand, weight, category, width, min(id) as id 
FROM media_new group  
WHERE type = 'Media'  
GROUP BY title, group_id, description, brand, weight, category, width -- <--  Looky here
ORDER BY id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hieloCommented:
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";
1
aboo_sCommented:
'Media' by title ORDER BY id' at line 1
should be
 'Media' ORDER by title
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

petewinterAuthor Commented:
Many thanks
0
Ray PaseurCommented:
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

1
PortletPaulfreelancerCommented:
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)
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.