I managed to figure out a query that does what I want.
SELECT listID, COUNT(*) AS Occurrences
FROM componentsToLists
WHERE componentID = ANY(
SELECT components.id
FROM components
WHERE components.name IN("nut","bolt"))
GROUP BY listID
ORDER BY Occurrences DESC;
This ranks a set of lists based on the number of named parts the
contain in them. anyone either a) know how to rewrite this for
version < 4.1 or b) make it faster.
->Ben
On Aug 24, 2006, at 2:27 AM, Ben Lachman wrote:
> I am fairly new to SQL and have run into a problem I can't figure
> out. I am trying to construct a query that returns an occurrence
> ranked list of results.
>
> I have the following tables and fields:
>
> components
> --------------------
> id
> name
>
> componentsToPartsList
> -------------------------------------------
> componentID
> listID
>
> partsLists
> ------
> id
> name
>
> I want to get results ranked by the number of components listed in
> a query that are in the same list. For example, list id 1 with the
> name 'robot' has parts bolt (component id 1), pincer (component id
> 3), and wheel (component id 4). List id 2 with name 'nuts and
> bolts' has bolt and nut (component id 2) only in it. If I pass in
> bolt, pincer and wheel I'd like to get back first 'robot' and then
> 'nuts and bolts.' If I pass in nut and bolt I'd like to get back
> 'nuts and bolts' and then 'robot.' I have a query that will return
> the relevant partsListIDs:
>
> SELECT componentsToPartsList.listID
> FROM componentsToPartsList
> WHERE componentsToPartsList.componentID IN(
> (SELECT components.id FROM components WHERE components.name =
> "nut"),
> (SELECT components.id FROM components WHERE components.name =
> "bolt");
> }
>
> But in the case of this query there are duplicates in the result. I
> would like to use these duplicates to rank (I assume using ORDER
> BY) the results based on the number of duplicates. Can anyone
> recommend a good method of doing this?
>
> ->Ben
