I thought I found a solution for a moment there. I created a query that uses "SELECT TOP 1" in subqueries for each "text" column I'm selecting. However SQL Server returns the error "The text, ntext, and image data types are invalid in this subquery or aggregate".
> What I'm doing is grouping on a field in a table and selecting a few > other fields at the same time. This requires either grouping on the > other fields selected or using an aggregate function. However a couple > of the fields are of type "text" which SQL Server does not want to run > an aggregate or group on. > > This works in Access with the First/Last aggregate functions but I > haven't found a way to accomplish this in SQL Erver. For example, in > Access the following query works. The Notes field in the example is of > type "text": > > SELECT PrimaryKey, AccountID, First(ClientName), First(Notes) > FROM Table > GROUP BY PrimaryKey, AccountID > > The only ideas I have are to run multiple queries to build up the > query object, or select all and then delete duplicate records after > the face. I don't like either of these options unless absolutely > necessary. It just feels like I'm overlooking something that would > make this possible. > > Any ideas? Thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293146 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
