List:General Discussion« Previous MessageNext Message »
From:<ric Date:December 22 2002 6:20am
Subject:Re: Return every Nth row in a result set
View as plain text  
Dear Spamsucks86,

    With Mysql 4.0.x I think you are limited to either performing the
algorithm at the client side as James suggested or using temporary tables
and mysql variables at the server side.

create table test (var int);
insert into test values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

create temporary table nth select *,@a:=@a+1,@a%2 as modulo from test;
select * from nth where modulo=0;

or alternatively,

create temporary table nth select *,@a:=@a+1 as row_count from test;
select * from nth where row_count%2=0;

Of course you would also need some order by's in there to make sure the
ordering of the nth value is to your liking.

Or if you happen to be using mysql 4.1 something like,

    select * from (select *,@a:=@a+1 as rc from test) rc where rc%2=0;

will do the job.

Richard.

p.s. Great name.

----- Original Message -----
From: "SpamSucks86" <spamsucks86@stripped>
To: <mysql@stripped>
Sent: Sunday, December 22, 2002 4:37 AM
Subject: Return every Nth row in a result set


> What would be the SQL query to return every Nth row in a result set? So
> it would return the 5th row, the 10th row, the 15th row, etc. Thanks for
> any and all replies!
>
>

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