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
