From: "Gareth Hughes" <[EMAIL PROTECTED]>
> 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?
SELECT
TOP 10
DT.search_criteria,
ST.search_datetime,
ST.search_page
FROM
(
SELECT
DISTINCT
search_criteria
FROM
search_table
) DT
INNER JOIN search_table ST ON
DT.search_criteria = ST.search_criteria
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:257197
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
