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]