Re: Occurrence-based ranking [solved?]
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]
Re: Occurrence-based ranking [solved?]
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]
Occurrence-based ranking
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]
