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