List:General Discussion« Previous MessageNext Message »
From:Richard Clarke Date:December 22 2002 4:57pm
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:

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

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