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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293144 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
