List:General Discussion« Previous MessageNext Message »
From:SpamSucks86 Date:December 22 2002 4:13pm
Subject:RE: Return every Nth row in a result set
View as plain text  
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

Thread
Return every Nth row in a result setSpamSucks8622 Dec
  • RE: Return every Nth row in a result setJamesD22 Dec
  • 4.1 questions: subqueries which use dualric22 Dec
    • re: 4.1 questions: subqueries which use dualVictoria Reznichenko24 Dec
  • Re: Return every Nth row in a result setric22 Dec
    • RE: Return every Nth row in a result setSpamSucks8622 Dec
      • Re: Return every Nth row in a result setBenjamin Pflugmann22 Dec
        • RE: Return every Nth row in a result setSpamSucks8622 Dec
          • Re: Return every Nth row in a result setRichard Clarke22 Dec
          • Re: Return every Nth row in a result setric22 Dec
  • Re: 4.1 questions: subqueries which use dualRichard Clarke24 Dec
    • Re: 4.1 questions: subqueries which use dualDan Nelson24 Dec
  • Re: 4.1 questions: subqueries which use dualRichard Clarke24 Dec
    • Re: 4.1 questions: subqueries which use dualMark Matthews24 Dec