> Hi, I'm trying to find out if it's possible to limit the output of a query
> to one record. I have a database that is sorted in decending order and I
> only want the top record (the one with the biggest total) to
> display. I can do a work-a-round by setting a variable to "on" before the
> query output and turning it "off" after the first record is processed and
> then <cfif> skipping the rest of the records, but was hoping to do it in
> the query it's self.
I know I'm VERY late coming to this (being off ill for a week does that to
my CF-Talk responses), but I felt I had to comment on some of the replies
The main things to think about when dealing with things like this is speed -
you want the SQL response as fast as possible and the data transferred to CF
as fast as possible
What MAXROWS does is take the total data, and only look at the first row -
this is fine if you have a small table, but as soon as you hit the tens or
hundreds of thousands, using this is just insane
The reason for the insanity is that ODBC/OLEDB has to pass the data back to
CF - think of it handing the records back one at a time (unless you use
BLOCKFACTOR) and then imagine how long it would take to transfer 10,000
records
Using TOP in the SQL is the correct way to handle it as SQL stops as soon as
it's found the highest entry - if you have indexes on your table(s), then
this is very fast, esp if you're using the order of the primary key
As an example to this, one of our tables currently has over 500,000 rows,
each with TEXT field types (SQL Server ones, not Access ones) - if I did;
<cfquery name="myQuery" datasource="myDSN" maxrows="1">
SELECT * FROM myTable ORDER BY ID
</cfquery>
the database driver would have to send all 500,000 records (several MB of
data) to CF before the MAXROWS came into effect
<cfquery name="myQuery" datasource="myDSN">
SELECT TOP 1 * FROM myTable ORDER BY ID
</cfquery>
would take about 1/500,000 of the time (well, you've got to remove the
"talk" time for the database driver, but it's still over 1/100,000 of the
time)
Note, I've left the ORDER BY in the second query, this is to ensure that it
gets the first ID - this should still be faster than SELECT MIN(ID) AS myID
as it uses the index to return 1 row rather than finding the lowest entry,
although if you're using a primary key, both will return very quickly
(either 0ms or less than 10-15ms)
I know I got wordy, but I hope this helps explain things
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
