Re: CF SQL question
Yeah, I was pretty sure too, but thought maybe things had changed since MS SQL 2000. In any case, in SQL 2000, it fails with: text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. Is this still the case with MS SQL 2005? Cheers, Kris > I'm pretty certain you are not allowed to use regular string functions > on *text* data types in MS SQL 2000. > > If you want to check for length, try this: > > IF (DATALENGTH(@text_var) > 0) > > ~Brad > Inequality <> works just fine for MS SQL Server TSQL for versions 2000 > and > 2005. > > Teddy > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255736 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
I'm pretty certain you are not allowed to use regular string functions on *text* data types in MS SQL 2000. If you want to check for length, try this: IF (DATALENGTH(@text_var) > 0) ~Brad -Original Message- From: Teddy Payne [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 11:43 AM To: CF-Talk Subject: Re: CF SQL question Kris, Inequality <> works just fine for MS SQL Server TSQL for versions 2000 and 2005. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255687 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF SQL question
Kris, Inequality <> works just fine for MS SQL Server TSQL for versions 2000 and 2005. Teddy On 10/5/06, Kris Jones <[EMAIL PROTECTED]> wrote: > > I don't think you can do equality (inequality) comparisons on a text > field in TSQL or ANSI SQL. You can do LIKE comparisons. > > > I found what was causing the problem and maybe someone can explain why - > > -the field was originally set as a text field... I changed it to > nvarchar > > and the error went away using: > > > > where members.speakers <> '' > > > > the error appeared using the above code until I made that field data > type > > change > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255685 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF SQL question
I don't think you can do equality (inequality) comparisons on a text field in TSQL or ANSI SQL. You can do LIKE comparisons. > I found what was causing the problem and maybe someone can explain why - > -the field was originally set as a text field... I changed it to nvarchar > and the error went away using: > > where members.speakers <> '' > > the error appeared using the above code until I made that field data type > change > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255678 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
Should be where members.speakers != "" The exclamation point inverts the equals. -Original Message- From: Tim Laureska [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 7:56 AM To: CF-Talk Subject: CF SQL question I hope this is an easy one. using SQL Server 2000.. Have a query trying to test for empty fields. tried the following and other variations but no luck: where members.speakers IS NOT "" Get this error: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary. Any help would be appreciated Thanks Tim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255658 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF SQL question
I'm guessing as it's a SQL error and has a where clause then it is in the SQL ! Also for the length test in CF, I would go for len(tim(variableName) (whether you put 'GT 0' after this test is the subject of another recent thread ;-) ) On 10/5/06, Kris Jones <[EMAIL PROTECTED]> wrote: > Tim, > > I'm not entirely clear on where you're testing for empty fields, > whether it is in the SQL in your query, or whether you are checking > the field after the query has returned. So here's my 2 cents: > > If it's inside the SQL, you can't compare the field name to an empty > string with IS NOT, you must use equals (=), OR if you are comparing > to NULL, then you would use the IS NOT like so: > > select field1, field2 from table1 > where field1!="" > > OR > > select field1, field2 from table1 > where field1 is not NULL > > If you're checking field content in coldfusion after the query has > returned, then it would be something like this: > > blah blah blah > > Hope that helps. > Cheers, > Kris > > > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > > test for empty fields. tried the following and other variations but no luck: > > where members.speakers IS NOT "" > > > > Get this error: > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > > column names. Use a single space if necessary. > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255651 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF SQL question
Tim, the problem is, that you use double quotes instead of single quotes... Greetings / Grüsse Gert Franz Customer Care Railo Technologies GmbH [EMAIL PROTECTED] www.railo.ch Join our Mailing List / Treten Sie unserer Mailingliste bei: deutsch: http://de.groups.yahoo.com/group/railo/ english: http://groups.yahoo.com/group/railo_talk/ Tim Laureska schrieb: > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > > > > where members.speakers IS NOT "" > > > > Get this error: > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. > > > > Any help would be appreciated > > > > Thanks > > Tim > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255652 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
You need to use single quotes '' to delimit strings. -Original Message- From: Tim Laureska [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 9:11 AM To: CF-Talk Subject: RE: CF SQL question I'm testing for empty fields in the sql query - - - I've tried this: select field1, field2 from table1 where field1!="" and still get: Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary. -Original Message- From: Kris Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 9:05 AM To: CF-Talk Subject: Re: CF SQL question Tim, I'm not entirely clear on where you're testing for empty fields, whether it is in the SQL in your query, or whether you are checking the field after the query has returned. So here's my 2 cents: If it's inside the SQL, you can't compare the field name to an empty string with IS NOT, you must use equals (=), OR if you are comparing to NULL, then you would use the IS NOT like so: select field1, field2 from table1 where field1!="" OR select field1, field2 from table1 where field1 is not NULL If you're checking field content in coldfusion after the query has returned, then it would be something like this: blah blah blah Hope that helps. Cheers, Kris > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > where members.speakers IS NOT "" > > Get this error: > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255653 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
I found what was causing the problem and maybe someone can explain why - -the field was originally set as a text field... I changed it to nvarchar and the error went away using: where members.speakers <> '' the error appeared using the above code until I made that field data type change -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 9:08 AM To: CF-Talk Subject: RE: CF SQL question > I hope this is an easy one. using SQL Server 2000.. Have a > query trying to test for empty fields. tried the following > and other variations but no luck: > > where members.speakers IS NOT "" > > Get this error: > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use > empty object or column names. Use a single space if necessary. Use single quotes to delimit literal strings in SQL. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255654 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
Empty string -Original Message- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 9:02 AM To: CF-Talk Subject: RE: CF SQL question Are you truly testing for an empty string, or are you trying to test for NULL? > -Original Message- > From: Tim Laureska [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 05, 2006 8:56 AM > To: CF-Talk > Subject: CF SQL question > > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > > > > where members.speakers IS NOT "" > > > > Get this error: > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. > > > > Any help would be appreciated > > > > Thanks > > Tim > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255644 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF SQL question
I'm testing for empty fields in the sql query - - - I've tried this: select field1, field2 from table1 where field1!="" and still get: Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary. -Original Message- From: Kris Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 9:05 AM To: CF-Talk Subject: Re: CF SQL question Tim, I'm not entirely clear on where you're testing for empty fields, whether it is in the SQL in your query, or whether you are checking the field after the query has returned. So here's my 2 cents: If it's inside the SQL, you can't compare the field name to an empty string with IS NOT, you must use equals (=), OR if you are comparing to NULL, then you would use the IS NOT like so: select field1, field2 from table1 where field1!="" OR select field1, field2 from table1 where field1 is not NULL If you're checking field content in coldfusion after the query has returned, then it would be something like this: blah blah blah Hope that helps. Cheers, Kris > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > where members.speakers IS NOT "" > > Get this error: > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255647 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF SQL question
Tim you can try 'IS NOT NULL' which tests for completely empty field values in a table if this is what you are trying to test? On 10/5/06, Tim Laureska <[EMAIL PROTECTED]> wrote: > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > > > > where members.speakers IS NOT "" > > > > Get this error: > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. > > > > Any help would be appreciated > > > > Thanks > > Tim > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255645 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
> I hope this is an easy one. using SQL Server 2000.. Have a > query trying to test for empty fields. tried the following > and other variations but no luck: > > where members.speakers IS NOT "" > > Get this error: > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use > empty object or column names. Use a single space if necessary. Use single quotes to delimit literal strings in SQL. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255643 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF SQL question
Tim, I'm not entirely clear on where you're testing for empty fields, whether it is in the SQL in your query, or whether you are checking the field after the query has returned. So here's my 2 cents: If it's inside the SQL, you can't compare the field name to an empty string with IS NOT, you must use equals (=), OR if you are comparing to NULL, then you would use the IS NOT like so: select field1, field2 from table1 where field1!="" OR select field1, field2 from table1 where field1 is not NULL If you're checking field content in coldfusion after the query has returned, then it would be something like this: blah blah blah Hope that helps. Cheers, Kris > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > where members.speakers IS NOT "" > > Get this error: > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255641 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF SQL question
Tim Laureska wrote: > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > > > > where members.speakers IS NOT "" > > WHERE members.speakers <> '' Also you might need to add in a "AND members.speakers IS NOT NULL", if the column allows nulls. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255642 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
Try: Where members.speakers IS NOT NULL AND members.speakers <> '' ___ Dave Konopka Systems Programmer Wharton Computing and Information Technology -Original Message- From: Tim Laureska [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 8:56 AM To: CF-Talk Subject: CF SQL question I hope this is an easy one. using SQL Server 2000.. Have a query trying to test for empty fields. tried the following and other variations but no luck: where members.speakers IS NOT "" Get this error: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary. Any help would be appreciated Thanks Tim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255639 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF SQL question
Empty Field and NULL are different. If you want the NULL FIELDS then : where members.speakers IS NULL If you want empty Fields: where LEN(members.speakers) = 0 -Original Message- From: Tim Laureska [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 8:56 AM To: CF-Talk Subject: CF SQL question I hope this is an easy one. using SQL Server 2000.. Have a query trying to test for empty fields. tried the following and other variations but no luck: where members.speakers IS NOT "" Get this error: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary. Any help would be appreciated Thanks Tim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255640 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF SQL question
Are you truly testing for an empty string, or are you trying to test for NULL? > -Original Message- > From: Tim Laureska [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 05, 2006 8:56 AM > To: CF-Talk > Subject: CF SQL question > > I hope this is an easy one. using SQL Server 2000.. Have a query trying to > test for empty fields. tried the following and other variations but no luck: > > > > where members.speakers IS NOT "" > > > > Get this error: > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or > column names. Use a single space if necessary. > > > > Any help would be appreciated > > > > Thanks > > Tim > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255638 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CF SQL question
I hope this is an easy one. using SQL Server 2000.. Have a query trying to test for empty fields. tried the following and other variations but no luck: where members.speakers IS NOT "" Get this error: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary. Any help would be appreciated Thanks Tim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255634 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (CF) SQL Question
Thanks. Duh. I knew it had to be easier than I was making it. I got it working with an inner join on a subquery, but did not think it was the easiest solution. Thanks, Jerry Johnson On 7/18/05, James Holmes <[EMAIL PROTECTED]> wrote: > I think this would work in Oracle at least and probably in a lot of > other DMBS (probably not MySQL) - note this is untested. > > SELECT * > FROM MyTable T > WHERE SEQUENCENUMBER = ( > Select MAX (SEQUENCENUMBER) > FROM MyTable > WHERE ITEMID = T.ITEMID >) > > On 7/18/05, Jerry Johnson <[EMAIL PROTECTED]> wrote: > > Can someone point me to a tutorial/manual that will help me get the > > following? I've done it before, but cannot for the life of me remember > > the _right_ way to do this in one query. > > > > table has recID (unique), itemID (not unique) and sequence number. > > > > How do I write a select to only return records with the max sequence > > number for each itemID? > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212116 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: (CF) SQL Question
I think this would work in Oracle at least and probably in a lot of other DMBS (probably not MySQL) - note this is untested. SELECT * FROM MyTable T WHERE SEQUENCENUMBER = ( Select MAX (SEQUENCENUMBER) FROM MyTable WHERE ITEMID = T.ITEMID ) On 7/18/05, Jerry Johnson <[EMAIL PROTECTED]> wrote: > Can someone point me to a tutorial/manual that will help me get the > following? I've done it before, but cannot for the life of me remember > the _right_ way to do this in one query. > > table has recID (unique), itemID (not unique) and sequence number. > > How do I write a select to only return records with the max sequence > number for each itemID? ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212115 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
(CF) SQL Question
Can someone point me to a tutorial/manual that will help me get the following? I've done it before, but cannot for the life of me remember the _right_ way to do this in one query. table has recID (unique), itemID (not unique) and sequence number. How do I write a select to only return records with the max sequence number for each itemID? Definitely Monday, Jerry Johnson ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212113 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
