Tim
that was my understanding - that the DB would still retrieve all rows and CF
limit the output - which I could have done but was trying to avoid and hence
find an alternative method to just bring back the required row (without the
table having a column I can use for this purpose)
this MySQL clause stated below sounds like exactly what I was trying to
achieve in SQL but I am using Access
_______________________________________________________
* Regards,
Richard Lovelock
Westminster City Council - Web Support
Cap Gemini Ernst & Young
Southbank
95 Wandsworth Road
London
SW8 2HG
( 0870 906 7482
_______________________________________________________
-----Original Message-----
From: Tim Blair [mailto:[EMAIL PROTECTED]
Sent: 06 October 2003 10:43
To: [EMAIL PROTECTED]
Subject: RE: [ cf-dev ] Rownumber in query
> <cfoutput query="query" STARTROW="#iStart#"
> MAXROWS="#iMessagesPerPage#">
Does this not actually perform the query "unrestricted" and then CF does
the limiting? So that method's not so good if you've got a query qhich
would return 1,000,000 rows and you only want to see 10 of them...
I like the MySQL LIMIT clause - you specify the number of rows to
retrieve and the offset to start the retrieve from which puts the work
onto the DB server not the CF server and also means you're not pushing
1,000,000 rows of data across the network etc:
>From http://www.mysql.com/doc/en/SELECT.html :
------------------------------8<--------------------------
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must be integer constants. With one argument, the value specifies the
number of rows to return from the beginning of the result set. With two
arguments, the first specifies the offset of the first row to return,
the second specifies the maximum number of rows to return. The offset of
the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL
also supports the syntax: LIMIT row_count OFFSET offset.
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result
set, you can use -1 for the second parameter:
mysql> SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
If one argument is given, it indicates the maximum number of rows to
return:
mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.
------------------------------8<--------------------------
Tim.
-------------------------------------------------------
RAWNET LTD - Internet, New Media and ebusiness Gurus.
Visit our new website at http://www.rawnet.com for
more information about our company, or call us free
anytime on 0800 294 24 24.
-------------------------------------------------------
Tim Blair
Web Application Engineer, Rawnet Limited
Direct Phone : +44 (0) 1344 393 441
Switchboard : +44 (0) 1344 393 040
-------------------------------------------------------
This message may contain information which is legally
privileged and/or confidential. If you are not the
intended recipient, you are hereby notified that any
unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such
notification notwithstanding, any comments, opinions,
information or conclusions expressed in this message
are those of the originator, not of rawnet limited,
unless otherwise explicitly and independently indicated
by an authorised representative of rawnet limited.
-------------------------------------------------------
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
********************************************************************************************
" This message contains information that may be privileged or confidential and
is the property of the Cap Gemini Ernst & Young Group. It is intended only for
the person to whom it is addressed. If you are not the intended recipient, you
are not authorized to read, print, retain, copy, disseminate, distribute, or use
this message or any part thereof. If you receive this message in error, please
notify the sender immediately and delete all copies of this message ".
********************************************************************************************
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
