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:benjamin-mysql@stripped]
Sent: Sunday, December 22, 2002 10:41 AM
To: SpamSucks86
Cc: mysql@stripped; ric@stripped
Subject: Re: Return every Nth row in a result set
Hello.
On Sun 2002-12-22 at 08:56:43 -0500, spamsucks86@stripped 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.
--
benjamin-mysql@stripped
