Posted by Erland Sommarskog on 10/01/61 11:22

(david@iaction.com) writes:
> I have two tables:
>
> workgroups (wg_id, wg_name)
> workgroups_keywords (wgk_wg_id, wgk_keyword)
>
> Each workgroup has an associated list of one or more keywords.
>
> What I want do to at first was given a particular list of keywords
> bring back a list of workgroups that have at least one matching keyword
> associated with it.
>
> I have the following query:
>
> select distinct(wg_id), wg_name
> from workgroups, workgroups_keywords
> where
> wgk_keyword in (#QuotedValueList(Keywords.wgk_keyword)#)
> and
> wg_id = wgk_wg_id
> order by wg_name

This syntax looks funny to me. Are you really using Microsoft SQL Server?

> This works great.
>
> However, is there a way in a single query to order the returned rows by
> the number of keywords that are found to be matching (in other words an
> order by relevancy, the more keywords that match the more relevant the
> returned row)?

That sounds doable. It would be something like

SELECT wg_nmae, COUNT(*)
FROM ...
GROUP BY wg_name
ORDER BY 2

But since I don't know exactly how this Keywords.wgk_keyword works,
and I'm uncertain that you are using MS SQL Server, I refrain from a
complete answer.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]