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]
