Try the following:
SELECT a.*
FROM artifacts a left join artifacts higher
on a.product_id = higher.product_id
and higher.plan_submission_number > a.plan_submission_number
WHERE a.product_id = '1'
AND higher.product_id is null;
For anyone following this, I'm using some sample code, which should
always be included when you want someone to
try to solve your SQL problems. Please correct me if this is
inappropriate data.
drop table if exists artifacts;
create table artifacts ( product_id int, plan_submission_number int );
insert into artifacts values ( 1, 1 ); -- is highest for product_id 1.
insert into artifacts values ( 2, 1 );
insert into artifacts values ( 2, 2 ); -- is highest for product_id 2.
insert into artifacts values ( 3, 1 );
insert into artifacts values ( 3, 2 );
insert into artifacts values ( 3, 3 ); -- is highest for product_id 3.
Luckily, LEFT JOIN solves many problems for you. By self-joining the
table, and looking for adjoining records to have a higher plan number,
you'll have table instance 'higher' always greater than those in 'a'.
And with the left join, you'll show 'a' even when there's no 'higher'
record meeting that qualification. Now add on the filter
'higher.product_id is null' and you'll only show those records from 'a'
where there is no matching higher record.
As long as product_id is indexed and very selective (relatively few
plans per product_id), this will be an efficient query.
Hope that helps,
Kevin
> -----Original Message-----
> From: Mark Wilson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 08, 2003 6:35 AM
> To: Mysql Mailing List
> Subject: Easy (?) conditional SELECT
>
>
> 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/mysql?> [EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
