List:General Discussion« Previous MessageNext Message »
From:Ben Lachman Date:August 25 2006 11:40pm
Subject:Re: Occurrence-based ranking [solved?]
View as plain text  
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

Thread
Occurrence-based rankingBen Lachman24 Aug
  • Re: Occurrence-based ranking [solved?]Ben Lachman26 Aug
    • Re: Occurrence-based ranking [solved?]Peter Brawley26 Aug