Re: How do I determine the row number or key when table has no key fields

2004-04-12 Thread Kevin Carlson 
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]

RE: How do I determine the row number or key when table has no key fields

2004-04-02 Thread Andy Eastham 
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

2004-04-02 Thread Andy Ford 
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]