I do not use MSSQL much now days but ran into this, this morning and what I did is just converted it to a varchar because only needed the first 100 characters anyway. Was something like this:
SELECT PRIMARYKEY, ACCOUNTID, CONVERT(VARCHAR(100), CLIENTNAME) CLIENTNAME, CONVERT(VARCHAR(100), NOTES) NOTES FROM TABLE GROUP BY PRIMARYKEY, ACCOUNTID, CONVERT(VARCHAR(100), CLIENTNAME) CLIENTNAME, CONVERT(VARCHAR(100), NOTES) NOTES I saw some other ways of doing it vie my searches online but that way just seemed to meet my needs the best. On Nov 12, 2007 1:41 PM, Daniel Roberts <[EMAIL PROTECTED]> wrote: > 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 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293148 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
