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=peter.brawley@earthlink.net



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