RE: One record query output
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
RE: One record query output
Can you use the maxrows attr within cfquery ? Chris Christopher Conrad Senior.Programmer.Analyst Advanced.Technology.Group The.Limited.Inc http://www.limited.com (o) 614.415.1181 (c) 614.523.0532 -Original Message- From: Michael Gribbin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 19, 2001 10:10 PM To: CF-Talk Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
RE: One record query output
cfloop query="queryname" cfif CurrentRow eq 1 ...display content cfelse cfbreak /cfif /cfloop Or you could just do this:-) cfoutput query="queryname" startrow=1 maxrows=1 ...display content /cfoutput -Original Message- From: Michael Gribbin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 20 March 2001 2:10 PM To: CF-Talk Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
Re: One record query output
Michael, You can use the maxrows="1" in the cfquery statement. example: CFQUERY NAME="queryname" DATASOURCE="datasourcename" maxrows="1" select field1,field2 from table /CFQUERY Dan - Original Message - From: "Michael Gribbin" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Monday, March 19, 2001 10:09 PM Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
RE: One record query output
You can use the MAXROWS attribute on CFQUERY and/or on CFOUTPUT. That should do it, Lee. -Original Message- From: Michael Gribbin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 20 March 2001 14:10 To: CF-Talk Subject: One record query output Hi, I'm trying to find out if it's possible to limit the output of a query to one record. IMPORTANT NOTICE: This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. ~~ 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
RE: One record query output
Why not let SQL so the work for you? SELECT TOP 1 Total_Column, other_columns FROM Table HTH, Jeff Garza Web Developer/Webmaster Spectrum Astro, Inc. 480.892.8200 [EMAIL PROTECTED] http://www.spectrumastro.com -Original Message- From: Michael Gribbin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 19, 2001 8:10 PM To: CF-Talk Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
Re: One record query output
Try max(column_value) or min(column_value) in where clause - Original Message - From: "Michael Gribbin" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Monday, March 19, 2001 10:09 PM Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
RE: One record query output
Surely maxrows="1" relies on the table being sorted correctly (by the amount field) SELECT TOP 1 * FROM tbl_Whatever ORDER BY WhateverIDontCare is much more supportable because it will always get the maximum value. Or am I mistaken? -Original Message- From: Dan Blickensderfer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 20 March 2001 15:18 To: CF-Talk Subject: Re: One record query output Michael, You can use the maxrows="1" in the cfquery statement. example: CFQUERY NAME="queryname" DATASOURCE="datasourcename" maxrows="1" select field1,field2 from table /CFQUERY Dan - Original Message - From: "Michael Gribbin" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Monday, March 19, 2001 10:09 PM Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
Re: One record query output
2 options : 1. select the MAX record from your SQL query, so you only have 1 record result return, use the MAX() function. 2. cfoutput query="#q#" startrow="1" maxrows="1" - Original Message - From: "Conrad, Christopher" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Tuesday, March 20, 2001 11:16 AM Subject: RE: One record query output Can you use the maxrows attr within cfquery ? Chris Christopher Conrad Senior.Programmer.Analyst Advanced.Technology.Group The.Limited.Inc http://www.limited.com (o) 614.415.1181 (c) 614.523.0532 -Original Message- From: Michael Gribbin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 19, 2001 10:10 PM To: CF-Talk Subject: One record query output 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. Possible? Thanks in advance. Michael Gribbin Digital Marketing Alliance ~~ 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
