From: Peter Brawley Date: August 26 2006 12:39am Subject: Re: Occurrence-based ranking [solved?] List-Archive: http://lists.mysql.com/mysql/201380 Message-Id: <44EF983A.7050407@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-44EF983A639C=======" --=======AVGMAIL-44EF983A639C======= Content-Type: multipart/alternative; boundary=------------020601040105020104080204 --------------020601040105020104080204 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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@stripped > > > > --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 > > --------------020601040105020104080204 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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@stripped

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

--------------020601040105020104080204-- --=======AVGMAIL-44EF983A639C======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-44EF983A639C=======--