"Kristen G. Thorson" <[EMAIL PROTECTED]> wrote on 10/14/2005
09:15:21 AM:
> I need to convert this query into one without a subquery:
>
> SELECT
> r.selector_forms_results_max_points AS points,
> r.selector_forms_results_description AS description,
> FROM selector_forms_results AS r
> WHERE r.selector_forms_id = 1
> AND r.selector_forms_results_max_points = (
> SELECT DISTINCT
> r.selector_forms_results_max_points AS max_points
> FROM selector_forms_results AS r
> WHERE r.selector_forms_results_max_points <= 12
> ORDER BY r.selector_forms_results_max_points DESC LIMIT 1
> );
>
> Given selector_forms_results_max_points like below:
> 9
> 9
> 9
> 11
> 11
> 11
> 11
> 13
> 13
>
> and selector_forms_results_max_points target of 12, I want the maximum
> of the set of selector_forms_results_max_points that are less than 12.
> So in this case, I want all rows where selector_forms_results_max_points
> = 11.
>
> I hope I have explained this somewhat clearly. I cannot see how this
> can be accomplished with a join, but my sql skills are rudimentary.
>
> Thanks for any help. Table structure and sample data follow.
>
>
> kgt
>
> Table:
>
> CREATE TABLE `selector_forms_results`
> (
> `selector_forms_results_id` int(11) NOT NULL
> auto_increment,
> `selector_forms_id` int(11) NOT NULL default
> '0',
> `selector_forms_results_description` varchar(255) NOT NULL default
> '',
> `selector_forms_results_max_points` int(11) NOT NULL default
> '0',
> PRIMARY KEY
> (`selector_forms_results_id`),
> KEY `selector_forms_results_max_points`
> (`selector_forms_results_max_points`) );
>
> And some sample data:
>
> insert into `selector_forms_results` values (1,1,'Pro1 180',9);
> insert into `selector_forms_results` values (2,1,'Iron Eagle 180',9);
> insert into `selector_forms_results` values (3,1,'RR 180',9);
> insert into `selector_forms_results` values (4,1,'Pro1 200',11);
> insert into `selector_forms_results` values (5,1,'Iron Eagle Platinum
> 200',11);
> insert into `selector_forms_results` values (6,1,'RR 200',11);
> insert into `selector_forms_results` values (7,1,'IK 200',11);
> insert into `selector_forms_results` values (8,1,'Pro1 215',13);
> insert into `selector_forms_results` values (9,1,'Iron Eagle Platinum
> 215',13);
>
The only advantages of doing this style of query as a subquery is that you
do not need to explicitly create/destroy a temporary table (the engine
does that for you behind the scenes) and you can write it in one
statement. In my experience, the subquery versions are usually noticably
slower. YMMV.
Here is a non-subquery equivalent of your particular query:
CREATE TEMPORARY TABLE tmpMAX (
KEY (`selector_forms_id`)
) SELECT
`selector_forms_id`
, MAX(`selector_forms_results_max_points`) as max_points
FROM `selector_forms_id`
WHERE `selector_forms_results_max_points`<=12
GROUP BY `selector_forms_id`;
SELECT
r.`selector_forms_results_max_points` AS points,
r.`selector_forms_results_description` AS description,
FROM `selector_forms_results` AS r
INNER JOIN `tmpMAX` tm
on r.`selector_forms_id` = tm.`selector_forms_id`
AND r.`selector_forms_results_max_points` = tm.`max_points`
WHERE r.`selector_forms_id` = 1;
DROP TEMPORARY TABLE tmpMAX;
I made this a little less restrictive than your original query so that you
could run the query against other `selector_form_id` values if you wanted.
Instead of using a temporary table, I could have used a single variable to
hold the max value as you only wanted the list for a single value of
`selector_forms_id`.
Does this help?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
