Create a view to combine all of your data and select a distinct search from the view.
Teddy On 10/18/06, Gareth Hughes <[EMAIL PROTECTED]> 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. > > 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:257194 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
