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/[EMAIL PROTECTED]
--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
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
