Re: workaround? : Limit in subquery not allowed
On Feb 6, 2008 6:40 AM, Britske <[EMAIL PROTECTED]> wrote: > SELECT * FROM prices WHERE prices.productid IN (SELECT id FROM priducts > ORDER BY id LIMIT 0, 1000) > > However, I'm getting an error-message stating that Limit is not allowed in a > subquery. > How would you approach this? SELECT * FROM prices JOIN (SELECT id FROM products ORDER BY id LIMIT 0, 1000) AS products ON (prices.productid = products.id) - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: workaround? : Limit in subquery not allowed
Hi, On Feb 6, 2008 6:40 AM, Britske <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm doing some clientside aggregation on rows (which represent prices ) > which sit in a huge table. I therefore want to import these rows in chunks, > but for this aggregation code to operate correctly I need to be sure that > all prices of a certain product are contained in the same chunk. > > To me it seemd logical to do something like this: > > SELECT * FROM prices WHERE prices.productid IN (SELECT id FROM priducts > ORDER BY id LIMIT 0, 1000) > > However, I'm getting an error-message stating that Limit is not allowed in a > subquery. > How would you approach this? I would fetch a list of IDs into the application, then put them into the IN() list. IN() subqueries will not perform well even if you didn't have this LIMIT problem. Actually, I'd probably use mk-archiver and a plugin to do this, because it uses a non-backtracking strategy that is very efficient. http://maatkit.sourceforge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
workaround? : Limit in subquery not allowed
Hi, I'm doing some clientside aggregation on rows (which represent prices ) which sit in a huge table. I therefore want to import these rows in chunks, but for this aggregation code to operate correctly I need to be sure that all prices of a certain product are contained in the same chunk. To me it seemd logical to do something like this: SELECT * FROM prices WHERE prices.productid IN (SELECT id FROM priducts ORDER BY id LIMIT 0, 1000) However, I'm getting an error-message stating that Limit is not allowed in a subquery. How would you approach this? Thanks, Geert-Jan -- View this message in context: http://www.nabble.com/workaround--%3A-Limit-in-subquery-not-allowed-tp15306494p15306494.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT in subquery or GROUP_CONCAT
On 17/11/2005, Peter Brodersen wrote: > I would like to select top three from different parts in the same > table, e.g. for the following data set: USE test; DROP TABLE IF EXISTS foo; CREATE TABLE foo ( fid INT NOT NULL, d INT NOT NULL ); INSERT INTO foo VALUES (1, 10), (1, 20), (1, 30), (1, 40), (2, 10), (2, 20), (2, 30), (3, 10), (4, 10), (4, 20), (4, 20), (4, 20), (4, 30), (5, 10), (5, 20), (5, 50), (5, 50), (5, 50), (5, 50); SELECT f1.fid, f1.d ,COUNT(f1.fid) AS Ties FROM foo f1 WHERE (SELECT COUNT(*) FROM foo f2 WHERE f2.fid = f1.fid AND f2.d > f1.d ) < 3 -- top 3 GROUP BY f1.fid, f1.d ORDER BY f1.fid, f1.d DESC; which also tries to handle ties. If you remove the 'COUNT() AS Ties' and the GROUP BY, you can have more than three results per fid when there are ties. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT in subquery or GROUP_CONCAT
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]
LIMIT in subquery or GROUP_CONCAT
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 .. 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/ALL/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... -- - Peter Brodersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit in subquery
Hi, I use ther version 4.1.11, and when I run this query : SELECT c.id,c.subject,c.res_type,c.news_id,c.com_thread,c.timestamp + 0 as timestamp FROM comments c WHERE c.id IN (SELECT d.id FROM comments d WHERE d.res_type=1 GROUP BY d.news_id ORDER BY d.id DESC LIMIT 5 ) ORDER BY c.timestamp DESC; ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' Does anyone know if this limit will be lifted and when ? Or does anyone know how to write this query in another way ? I try to pull the last 5 comments that match "res_type=1" but with only one by news_id and only the last 5 comments. (I guess I could use a temp table, feed with the 5 matching id, then join it with the comments table) -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
