List:General Discussion« Previous MessageNext Message »
From:Ben Lachman Date:August 24 2006 6:27am
Subject:Occurrence-based ranking
View as plain text  
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