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. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
- Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 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. I don't know any way to do this directly with SQL, but I could be wrong, stored procedures may help you here, I do not use mysql 5, so I have not played with them yet. I would probably do this with a second table, and some SQL in the middle of it all to make it happen. Your second table would simply store the last id that you selected. So, you select from your second table, to get the last id you retrieved. Them you select from the primary table, where id retreived_id limit 2, once you have that, take the ID from the last record in the 2 you just got back, and insert/update that data into your second table. Make sure you add in app logic to deal with cases where the second table has never been inserted into, also when you wrap your record set, it will need to be reset, but I think you get the idea. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Returning records in a circle
I ran into this same type of question the other evening at a local linux group. I think that once you reach the end of the results set the only way to start back at the beginning of that results set is to do the query again. Once option - do your query and grab all the rows load them into a data structure - ie - a list of associative arrays Then all you need to do is incrementally go through the array. when you hit the end, just reset the index back to 0. -Original Message- From: Steffan A. Cline [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:52 PM To: mysql@lists.mysql.com Subject: Re: Returning records in a circle Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com 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
Re: Returning records in a circle
I'm sorry but I think you're all making this more complicated than it needs to be. I could easily write a Java program that obtained a result set, then processed that set in almost any way you wanted, including two records at a time. I could probably do the same in Perl or any other language supported by MySQL, although it might take a bit of time to learn the necessary parts of the language. Processing a result set is a very well-understood process and has been widely done millions of times in dozens of languages. You should not need to add any columns to the table to do it either. I might be able to offer more detail - and maybe even an example! - if a few things were explained to me. I'm still not clear on why two records are being processed at a time and what the relationship is between the records. About the only time I see twinned records are when someone is doing an accounting application where each debit is matched by an offsetting credit. But this doesn't seem to the case here. Perhaps you don't need to process two records at once? Also, am I right in assuming that you are allowing new records to be written to the table - and allowing existing records to be updated - while you run your query? If yes, is it possible that the keys of the new records will be interspersed with the existing keys? Or will new records always have a key value that is higher than the highest previous key value? If inserts and updates are happening, do they absolutely HAVE to happen simultaneously with your query? Or could you store them off to the side briefly or even just suspend the insert and update applications while the query runs? If there are no inserts and updates to worry about, you should be able to avoid any updates of duplicates by simply sorting all of the desired rows into order based on the key and then processing them one (or two) at a time. Things get a bit trickier if the table is being updated/inserted while your new program is running. I don't want to say much more until you've clarified what it is you are doing -- Rhino - Original Message - From: George Law [EMAIL PROTECTED] To: Steffan A. Cline [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, May 12, 2006 3:33 PM Subject: RE: Returning records in a circle I ran into this same type of question the other evening at a local linux group. I think that once you reach the end of the results set the only way to start back at the beginning of that results set is to do the query again. Once option - do your query and grab all the rows load them into a data structure - ie - a list of associative arrays Then all you need to do is incrementally go through the array. when you hit the end, just reset the index back to 0. -Original Message- From: Steffan A. Cline [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:52 PM To: mysql@lists.mysql.com Subject: Re: Returning records in a circle Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can
