select id, max(otherfield) as otherfield from table, othertable where table.id = othertable.id group by id order by otherfield
This is just off the top of my head, but I hope it gets the idea across. --Ben Doom Mike Little wrote: > not sure i follow ben, i don't want to include the category/collection info > in the GROUP BY as this will give me the exact same result as not using GROUP > BY? > > could you give an example of using the min/max on the other columns? > > thanks mate. > > mike > > > >> When you instruct the system to only return one of a group like that, >> you have to tell it which of the possible selections to make. So, you >> need to either group by the other columns (which will return a row for >> every distinct entry in that column) or use an aggregate function to >> only return one. You could return the min or max or similar of the >> other columns, which may solve your problem. >> >> --Ben >> >> Mike | NZSolutions Ltd wrote: >>> mike > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269482 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
