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. For example let's say the most recent 10 entries includes the search phrase 'foo' 3 times - if I selected TOP 10 I'd actually only have 8 unique results. I've tried using DISTINCT in various ways but, because I need the 'search_datetime' and 'search_page' columns in the select list, the 'foo' entries are all considered unique. So, I'm wondering if it's possible to get unique values for my search phrases but include the other column data? I'm stuck. Any ideas? Thanks Gareth ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:257190 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
