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
