List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 26 2006 12:39am
Subject:Re: Occurrence-based ranking [solved?]
View as plain text  
Ben

/>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. /

SELECT l.listID, COUNT(*) AS Occurrences
FROM componentsToLists AS l
INNER JOIN components AS c ON l.componentID=c.ID
WHERE c.name='nut' OR c.name='bolt'
GROUP BY l.listID
ORDER BY Occurrences DESC;

PB

-----


Ben Lachman wrote:
> 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
>
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.405 / Virus Database: 268.11.6/427 - Release Date: 8/24/2006
>
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.6/427 - Release Date: 8/24/2006
Thread
Occurrence-based rankingBen Lachman24 Aug
  • Re: Occurrence-based ranking [solved?]Ben Lachman26 Aug
    • Re: Occurrence-based ranking [solved?]Peter Brawley26 Aug