RE: Return every Nth row in a result set
I really don't want to do this client side (I'd have to execute approximately 10 queries for every page load just for this small task). Selecting the entire table into a temp table to number the rows also seems rather inefficient. I was reading in a book at Barnes and Noble yesterday which said to use a query that looked something like this: SELECT a.id FROM documents as a, documents as b WHERE a.id = b.id GROUP BY a.id HAVING MOD(a.id,:n); I'm nearly positive that that isn't exactly what it said, but it was something like that. If anyone can come up with a way to do this without a temporary table and only one or two queries (using 3.x or 4.0) that'd be great. Thanks for the help guys. -Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Return every Nth row in a result set
Hello. On Sun 2002-12-22 at 08:56:43 -0500, [EMAIL PROTECTED] wrote: I really don't want to do this client side (I'd have to execute approximately 10 queries for every page load just for this small task). Selecting the entire table into a temp table to number the rows also seems rather inefficient. I was reading in a book at Barnes and Noble yesterday which said to use a query that looked something like this: SELECT a.id FROM documents as a, documents as b WHERE a.id = b.id GROUP BY a.id HAVING MOD(a.id,:n); I'm nearly positive that that isn't exactly what it said, but it was something like that. If anyone can come up with a way to do this without a temporary table and only one or two queries (using 3.x or 4.0) that'd be great. Thanks for the help guys. Well, the solution is already in there: they suggest using a HAVING clause to reduce the rows after the complete result set has been determined. And to use MOD(id, number) to select which rows to keep. MOD(id,10) will return 0 for multiples of 10. So, if you want every 10th rows, you would use SELECT * FROM your_table WHERE some_condition HAVING NOT MOD(id,10) If you still encounter problems, please elaborate. And include a real example of what you tried. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Return every Nth row in a result set
I can't do it by ID because what if a row in the middle somewhere gets deleted? I need to do it by the position in the table, and a static numbering column won't work. This is a solution someone on EFNet came up with: SET @rowcount=0; select docid,@rowcount:=@rowcount+1 as num FROM documents HAVING (num+1)%6=0 It works. However, if I do: SET @rowcount=0; SELECT docid,@rowcount:=@rowcount+1 as num FROM documents; The num associated with each docid is different than the ones that return from the first query. This seems weird, possibly a bug? Here are the results for you guys to chew on. As you will see, in the first result set, when docid=55, num=25 (25th record in the table). However, on the second result set, when docid=55, num=30. I can prove that docid=55 is actually the 25th record in the table because: SELECT docid FROM documents LIMIT 24,1 Returns docid=55. I'd love to hear what you guys think. Here's the result I promised: mysql select docid,@rowcount:=@rowcount+1 as num from documents limit 40; +---+--+ | docid | num | +---+--+ | 2 |1 | | 4 |2 | | 5 |3 | | 6 |4 | | 7 |5 | | 8 |6 | | 9 |7 | |10 |8 | |11 |9 | |12 | 10 | |13 | 11 | |14 | 12 | |15 | 13 | |16 | 14 | |17 | 15 | |20 | 16 | |21 | 17 | |34 | 18 | |35 | 19 | |36 | 20 | |37 | 21 | |39 | 22 | |40 | 23 | |41 | 24 | |55 | 25 | |56 | 26 | |59 | 27 | |61 | 28 | |77 | 29 | |80 | 30 | |81 | 31 | |82 | 32 | |83 | 33 | |84 | 34 | |85 | 35 | |86 | 36 | |88 | 37 | |93 | 38 | | 106 | 39 | | 109 | 40 | +---+--+ 40 rows in set (0.00 sec) mysql select docid,@rowcount:=@rowcount+1 as num FROM documents HAVING (num+1)%6=0 LIMIT 30; +---+--+ | docid | num | +---+--+ | 7 |6 | |12 | 12 | |17 | 18 | |36 | 24 | |55 | 30 | |80 | 36 | |85 | 42 | | 109 | 48 | | 119 | 54 | | 125 | 60 | | 136 | 66 | | 147 | 72 | | 152 | 78 | | 160 | 84 | | 166 | 90 | | 171 | 96 | | 178 | 102 | | 185 | 108 | | 191 | 114 | | 207 | 120 | | 213 | 126 | | 218 | 132 | +---+--+ 22 rows in set (0.00 sec) -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 10:41 AM To: SpamSucks86 Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Return every Nth row in a result set Hello. On Sun 2002-12-22 at 08:56:43 -0500, [EMAIL PROTECTED] wrote: I really don't want to do this client side (I'd have to execute approximately 10 queries for every page load just for this small task). Selecting the entire table into a temp table to number the rows also seems rather inefficient. I was reading in a book at Barnes and Noble yesterday which said to use a query that looked something like this: SELECT a.id FROM documents as a, documents as b WHERE a.id = b.id GROUP BY a.id HAVING MOD(a.id,:n); I'm nearly positive that that isn't exactly what it said, but it was something like that. If anyone can come up with a way to do this without a temporary table and only one or two queries (using 3.x or 4.0) that'd be great. Thanks for the help guys. Well, the solution is already in there: they suggest using a HAVING clause to reduce the rows after the complete result set has been determined. And to use MOD(id, number) to select which rows to keep. MOD(id,10) will return 0 for multiples of 10. So, if you want every 10th rows, you would use SELECT * FROM your_table WHERE some_condition HAVING NOT MOD(id,10) If you still encounter problems, please elaborate. And include a real example of what you tried. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Return every Nth row in a result set
. I can't do it by ID because what if a row in the middle somewhere gets deleted? I need to do it by the position in the table, and a static numbering column won't work. This is a solution someone on EFNet came up with: SET @rowcount=0; select docid,@rowcount:=@rowcount+1 as num FROM documents HAVING (num+1)%6=0 It works. However, if I do: No it doesn't. [snip] mysql select docid,@rowcount:=@rowcount+1 as num from documents limit 40; +---+--+ | docid | num | +---+--+ | 2 |1 | | 4 |2 | | 5 |3 | | 6 |4 | | 7 |5 | | 8 |6 | | 9 |7 | |10 |8 | [snip] mysql select docid,@rowcount:=@rowcount+1 as num FROM documents HAVING (num+1)%6=0 LIMIT 30; +---+--+ | docid | num | +---+--+ | 7 |6 | |12 | 12 | [snip] How is 7 the 6th element? Looks like it should be 8 to me. Shouldn't the first element, 2, be included in the result set also? If you don't include the first element in the result of give me every nth element then its never going to appear in the result set. Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Return every Nth row in a result set
. I can't do it by ID because what if a row in the middle somewhere gets deleted? I need to do it by the position in the table, and a static numbering column won't work. This is a solution someone on EFNet came up with: SET @rowcount=0; select docid,@rowcount:=@rowcount+1 as num FROM documents HAVING (num+1)%6=0 It works. However, if I do: No it doesn't. [snip] mysql select docid,@rowcount:=@rowcount+1 as num from documents limit 40; +---+--+ | docid | num | +---+--+ | 2 |1 | | 4 |2 | | 5 |3 | | 6 |4 | | 7 |5 | | 8 |6 | | 9 |7 | |10 |8 | [snip] mysql select docid,@rowcount:=@rowcount+1 as num FROM documents HAVING (num+1)%6=0 LIMIT 30; +---+--+ | docid | num | +---+--+ | 7 |6 | |12 | 12 | [snip] How is 7 the 6th element? Looks like it should be 8 to me. Shouldn't the first element, 2, be included in the result set also? If you don't include the first element in the result of give me every nth element then its never going to appear in the result set. Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Return every Nth row in a result set
What would be the SQL query to return every Nth row in a result set? So it would return the 5th row, the 10th row, the 15th row, etc. Thanks for any and all replies!
RE: Return every Nth row in a result set
seems like it would be easier to
write a perl script
#repeated SQL selects
$count=0;
$sth = $dbh-prepare(qq(select * from table LIMIT ?,?));
while ($sth){
$sth-execute($count,1);
$ref =$sth-fetchrow_arrayref();
print FILEHANDLE my item is $ref-[0],$ref-[1]...etc\n;
$count+=5;
}
#or use modulo
$count=0;
$sth = $dbh-prepare(qq(select * from table));
$sth-execute();
$ref =$sth-fetchrow_arrayref();
@stuff = @$ref; #dereference the array
$total = @stuff;#get item count
LINE: while ($total) {
next LINE unless ($count == 0||$count % 5 == 0){
print FILEHANDLE my item is $ref-[0],$ref-[1]...etc\n;
} continue { $count++; $total--;}
Jim
-Original Message-
From: SpamSucks86 [mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 21, 2002 8:38 PM
To: [EMAIL PROTECTED]
Subject: Return every Nth row in a result set
What would be the SQL query to return every Nth row in a result set? So
it would return the 5th row, the 10th row, the 15th row, etc. Thanks for
any and all replies!
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Return every Nth row in a result set
Dear Spamsucks86, With Mysql 4.0.x I think you are limited to either performing the algorithm at the client side as James suggested or using temporary tables and mysql variables at the server side. create table test (var int); insert into test values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); create temporary table nth select *,@a:=@a+1,@a%2 as modulo from test; select * from nth where modulo=0; or alternatively, create temporary table nth select *,@a:=@a+1 as row_count from test; select * from nth where row_count%2=0; Of course you would also need some order by's in there to make sure the ordering of the nth value is to your liking. Or if you happen to be using mysql 4.1 something like, select * from (select *,@a:=@a+1 as rc from test) rc where rc%2=0; will do the job. Richard. p.s. Great name. - Original Message - From: SpamSucks86 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, December 22, 2002 4:37 AM Subject: Return every Nth row in a result set What would be the SQL query to return every Nth row in a result set? So it would return the 5th row, the 10th row, the 15th row, etc. Thanks for any and all replies! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
