I ditto the grouping method. Then you will have a distinct list of the keywords, and you can use your aggregates like count() to get the number of times it was searched for, and order by that desc.
~Brad -----Original Message----- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 10:06 AM To: CF-Talk Subject: Re: Select distinct on multiple columns Gareth Hughes wrote: > I'm turning my brain to mush with a SQL conundrum and wonder if anyone can > offer some help. > > I keep a record of what people searched for, the date/time they searched for > it and the page they were on when they searched. My table consists of three > columns 'search_criteria', 'search_datetime' and 'search_page'. > > What I can't figure out is how to produce reports using distinct values such > as 'the 10 most recent searches' where I get ten unique results even if > there are duplicates in the most recent db entries. > Maybe something like... SELECT TOP 10 search_criteria, search_page, max(search_datetime) AS latestdate FROM tblSearches GROUP BY search_criteria, search_page ORDER BY latestdate DESC ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:257200 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
