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
