BTW, I tested the SELECT(MAX) part of this separately, and discovered that MySQL doesn't like the single quotes around the table name, so I took them out. Now THIS query works: SELECT MAX( plan_submission_number ) FROM artifacts WHERE product_id = '1' -- (returns '2')
But this one still doesn't: SELECT * from artifacts WHERE (product_id = '1' AND plan_submission_number = (SELECT MAX( plan_submission_number ) FROM artifacts WHERE product_id = '1') ) Although that seems as if it should be equivalent to the following hard-coded version: SELECT * FROM `artifacts` WHERE (product_id = '1' AND plan_submission_number = '2') -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 Quoting Mark Wilson <[EMAIL PROTECTED]>: > I have an app for which people can submit plans. > Each plan relates to a particular product. > A new plan can be submitted for the same product, so each plan has its own > submission number. (1,2,3...) > Each plan is composed of artifacts. > The (artifacts) table looks like this: > artifact_id INT > product_id INT > plan_submission_number INT > (etc) > > Task: get all the items for the most recent (i.e., highest) submission plan > for > a particular product. > > Since I'm relatively new to MySQL, and haven't mastered much beyond the most > basic SELECTs, much less JOINs, I'm not sure how to do this. I think the > following should work (for product_id = 1), but it returns a syntax error. > > SELECT * > FROM `artifacts` > WHERE ( product_id = '1' AND plan_submission_number = ( > SELECT MAX( plan_submission_number ) > FROM 'artifacts' > WHERE product_id = '1' ) ) > --------------- > Error message: > 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 'SELECT MAX( > plan_submission_number ) > FROM 'artifacts' > WHERE p > --------------- > What am I missing? Thanks.... > > - Mark > > -- > Mark Wilson, Computer Programming Unlimited (cpuworks.com) > Web : http://cpuworks.com Tel: 410-549-6006 > Email: [EMAIL PROTECTED] Fax: 410-549-4408 > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
