Learn thy SQL ;) Let's say you've got a table with cols "ID" and "MyValue" and your grouping by the "ID" field. If you add "MyValue" to the select list, sql needs to know which "MyValue" value to return when there are duplicates in the ID field.
Example: Table ID | MyValue 5 | goat 5 | bird Query: select ID,MyValue from table group by ID Results: ID | MyValue 5 | ??? (goat or bird) If you don't care which value is returned, just add a Max or Min to MyValue and you're done. select ID,Max(MyValue) as MyValue from table group by ID -Daniel Elmore -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Mast Sent: Wednesday, March 15, 2006 10:05 AM To: Dallas/Fort Worth ColdFusion User Group Mailing List Subject: [DFW CFUG] query group by question Hi All, I have a query and I'm grouping by 2 fields and the query dumps no problem with those 2 fields in the SELECT statement, but when I try and add a field to the SELECT statement I get the following error: Error Diagnostic Information ODBC Error Code = S1000 (General error) [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0122 - Column CPARTW or expression in SELECT list not valid. Can anyone point me in the right direction? Any help is greatly appreciated. Thanks, Ron _______________________ This e-mail and any files transmitted with it are confidential and are intended solely for the use of the individual to whom they are addressed. If you are not the intended recipient or the individual responsible for delivering the e-mail to the intended recipient, please be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/ _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
