Re: How do I determine the row number or key when table has no key fields
Andy Ford wrote: I thought LIMIT limited you to N number of CONCURRENT record. ie. limit 10 or limit 20 I believe Ross would like to select select 1000 records and then do a sub select of records 1-20 and then 21-40 on this record set LIMIT also allows you to specify a starting record, i.e. LIMIT 50, 100 so Ross could change the first parameter to accomplish this. Example: First query: LIMIT 0, 20 Second: LIMIT 20, 20 Third: LIMIT 40, 20 etc Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I determine the row number or key when table has no key fields
eg. say a table is created using: create table fred (f1 char(10), f2 int) Then it has neither keys nor an AUTO_INCREMENT field. Let's say 1000,000 records are then inserted into table fred. I then say 'select * from fred' and loop through results writing to a web page. I stop writing to the web page after say 20 records. The user hits 'next page'. I want to say 'select * from fred where ?field? ?value? Where ?field? and ?value? are what I want to know. Surely there is some kind of 'record number' or something available in mySQL for me to : 1. Retrieve and save 2. Query against I'm new to this mailing list. Apologies if I am asking this question of an inappropriate email address. Regards ... Ross . Ross Honniball JCU Bookshop Cairns Supervisor . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] . There are no problems. Only solutions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I determine the row number or key when table has no key fields
Ross, You'll need to do an order by on both columns (so you'll need to index both columns in a compound index), then use the LIMIT keyword which is designed for exactly this job. Alternatively, unload the data using mysqldump, then edit the table definition to have an autoincrement column, then reload the data. Find more information in the manual at http://www.mysql.com/doc/en/index.html Andy -Original Message- From: Ross Honniball [mailto:[EMAIL PROTECTED] Sent: 02 April 2004 13:48 To: [EMAIL PROTECTED] Subject: How do I determine the row number or key when table has no key fields eg. say a table is created using: create table fred (f1 char(10), f2 int) Then it has neither keys nor an AUTO_INCREMENT field. Let's say 1000,000 records are then inserted into table fred. I then say 'select * from fred' and loop through results writing to a web page. I stop writing to the web page after say 20 records. The user hits 'next page'. I want to say 'select * from fred where ?field? ?value? Where ?field? and ?value? are what I want to know. Surely there is some kind of 'record number' or something available in mySQL for me to : 1. Retrieve and save 2. Query against I'm new to this mailing list. Apologies if I am asking this question of an inappropriate email address. Regards ... Ross . Ross Honniball JCU Bookshop Cairns Supervisor . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] . There are no problems. Only solutions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I determine the row number or key when table has no key fields
I thought LIMIT limited you to N number of CONCURRENT record. ie. limit 10 or limit 20 I believe Ross would like to select select 1000 records and then do a sub select of records 1-20 and then 21-40 on this record set Or am I barking up the wrong tree? Andy On Fri, 2004-04-02 at 14:05, Andy Eastham wrote: Ross, You'll need to do an order by on both columns (so you'll need to index both columns in a compound index), then use the LIMIT keyword which is designed for exactly this job. Alternatively, unload the data using mysqldump, then edit the table definition to have an autoincrement column, then reload the data. Find more information in the manual at http://www.mysql.com/doc/en/index.html Andy -Original Message- From: Ross Honniball [mailto:[EMAIL PROTECTED] Sent: 02 April 2004 13:48 To: [EMAIL PROTECTED] Subject: How do I determine the row number or key when table has no key fields eg. say a table is created using: create table fred (f1 char(10), f2 int) Then it has neither keys nor an AUTO_INCREMENT field. Let's say 1000,000 records are then inserted into table fred. I then say 'select * from fred' and loop through results writing to a web page. I stop writing to the web page after say 20 records. The user hits 'next page'. I want to say 'select * from fred where ?field? ?value? Where ?field? and ?value? are what I want to know. Surely there is some kind of 'record number' or something available in mySQL for me to : 1. Retrieve and save 2. Query against I'm new to this mailing list. Apologies if I am asking this question of an inappropriate email address. Regards ... Ross . Ross Honniball JCU Bookshop Cairns Supervisor . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] . There are no problems. Only solutions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^' in:control developer, Telindus, RG27 9HY DDI: +44 1256 709211, GSM: +44 7810 636652 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]