List:General Discussion« Previous MessageNext Message »
From:Rhino Date:May 12 2006 6:20pm
Subject:Re: Returning records in a circle
View as plain text  
----- Original Message ----- 
From: "Steffan A. Cline" <steffan@stripped>
To: <mysql@stripped>
Sent: Friday, May 12, 2006 1:38 PM
Subject: Returning records in a circle


> Is there a way I can get a set of records incrementally such as to get 2
> then the next query get the next 2 then at the end of all records to get 
> the
> 2 from the beginning? I need to keep going incrementally by 2 in a circle.
>

Are you trying to get these rows purely via SQL at the command line or in an 
SQL script? Or would an application be an option for you?

If you are not willing to consider application code to grab the rows you 
want, the answer to your question is "maybe". SQL has always been intended 
to return ALL of the rows that satisfy a query with a single invocation of 
the query, no matter how many rows that is. So if your query says:

    select * from mytab;

you will normally get all of the rows that satisfy that query in one go, 
whether there are 0 rows, 100 rows, or a 100 million rows in the result.

You _might_ be able to get the results you want by using the LIMIT clause. 
I'm not sure what version of MySQL you are using but the LIMIT clause is 
described in the MySQL 3.23/4.0/4.1 manual on this page: 
http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that 
you'll still pretty much need some sort of script in order to keep executing 
the query to get the next two records and you may need to change the 
parameters of the LIMIT clause at the same time.

If you are willing to write application code, things get a lot easier. For 
instance, a Java program could easily grab rows from a result set for you 
two at a time, let you process them, then grab two more, etc. I expect that 
it would similarly easy to do the same thing in Perl and PHP and C.

In short, a program gives you a lot more ability to do what you want to do 
with your database data. But some shops have very little programming 
expertise and prefer to do everything via SQL. If you work for one of those 
shops, you might not be able to get your records two at a time with SQL 
alone, unless you can write a script that takes advantage of the LIMIT 
clause.

I don't pretend to know MySQL exhaustively so someone else may have another 
suggestion for you but the only two approaches I can think of that might 
meet your needs are to use the LIMIT clause or to write an application.

--
Rhino




-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006

Thread
Returning records in a circleSteffan A. Cline12 May
  • Re: Returning records in a circleRhino12 May
    • Re: Returning records in a circleSteffan A. Cline12 May
  • Using a stored procedure inside a viewChris Carrier12 May
  • Re: Returning records in a circleScott Haneda12 May
RE: Returning records in a circleGeorge Law12 May
  • Re: Returning records in a circleRhino12 May