Hello.
I've written a stored procedure that can help you. However, I'd like to
see a solution which will work for 4.1 as well.
Here is the results. First group is select from the original table,
second group is select from the temporary table with the results.
id value
1 10
1 20
1 30
1 40
1 50
2 10
2 20
2 30
2 40
3 10
3 20
4 10
id value
1 50
1 40
1 30
2 40
2 30
2 20
3 20
3 10
4 10
Table t1 must exists. The contents of p1.sql:
SELECT * FROM t1;
CREATE TEMPORARY TABLE tcur(id int,value int);
DROP PROCEDURE IF EXISTS query3;
DELIMITER $$
CREATE PROCEDURE query3()
DETERMINISTIC
BEGIN
DECLARE tid INT;
DECLARE cur2_value INT;
DECLARE cur1_value INT;
DECLARE no_more BOOLEAN default FALSE;
DECLARE cur1 CURSOR FOR SELECT DISTINCT id FROM t1 ORDER BY id;
DECLARE cur2 CURSOR FOR SELECT value FROM t1 WHERE id = tid ORDER BY
value DESC LIMIT 3;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET no_more = TRUE;
open cur1;
cur1_loop: LOOP
FETCH cur1 INTO tid;
IF no_more=TRUE THEN
LEAVE cur1_loop;
END IF;
open cur2;
cur2_loop: LOOP
FETCH cur2 INTO cur2_value;
IF no_more=TRUE THEN
LEAVE cur2_loop;
END IF;
INSERT INTO tcur SET id=tid, value=cur2_value;
END LOOP;
close cur2;
SET no_more=FALSE;
END LOOP;
close cur1;
END$$
DELIMITER ;
call query3();
SELECT * FROM tcur;
DROP TEMPORARY TABLE tcur;
The definition of t1:
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) default NULL,
`value` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Peter Brodersen wrote:
> Hi,
>
> (mysql server 5.0.15 running under linux)
>
> I suppose this is a classic task. I just hope MySQL 5.0 is able to give
> the right result.
>
> I would like to select top three from different parts in the same table,
> e.g. for the following data set:
>
> id,value
> 1,10
> 1,20
> 1,30
> 1,40
> 1,50
> 2,10
> 2,20
> 2,30
> 2,40
> 3,10
> 3,20
> 4,10
>
> =2E. I would like the output to be:
>
> id,value
> 1,50
> 1,40
> 1,30
> 2,40
> 2,30
> 2,20
> 3,20
> 3,10
> 4,10
>
> That is, the top 3 from each id. The id could be a key in another table
> instead (just containing 1,2,3,4 as rows). The numbers of different ids
> might vary thus a static set of UNIONs is no answer.
>
> I tried using a subquery using LIMIT inside, but I just got the
> following result:
> ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/A=
> LL/ANY/SOME subquery'
> It seems like this was a possibility in very early versions of MySQL 4.1.
>
> I tried with GROUP_CONCAT() as well, but even though there is an ORDER
> option, there is no LIMIT option here (feature request? :-) )
>
> Maybe using a variable to keep track of internal count...
>
> --=20
> - Peter Brodersen
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
