Convert Subquery - need to find max of a set

2005-10-14 Thread Kristen G. Thorson 

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);





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Convert Subquery - need to find max of a set

2005-10-14 Thread SGreen 
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

Re: Convert Subquery - need to find max of a set

2005-10-14 Thread Kristen G. Thorson 

[EMAIL PROTECTED] wrote:

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
 





Thank you, this works perfectly for me.  I don't even have any questions!


kgt





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]