November 15th, 2012, 09:11 AM
First item IN
Here is the structure of my table. It is a join table between "facets" and "faceted items":
CREATE TABLE `faceted_items_facets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`facet_id` int(11) NOT NULL,
`faceted_item_id` int(11) NOT NULL,
`sequence` int(11) DEFAULT NULL,
`value` varchar(300) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `value` (`value`),
KEY `item_id` (`faceted_item_id`),
FULLTEXT KEY `value_2` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Here's the basic idea of the query I'm trying to create:
SELECT * FROM faceted_items_facets WHERE faceted_item_id IN(1,2,3,4) AND facet_id = 555;
But the trouble is: there is a possibility that each "faceted item" may have multiple records for a given facet. That's not supposed to be the case, but there are errors in the database that I'm trying to clean up.
So, what I'd like to be able to do is grab the record for each faceted item with the highest id value (logically, the most recent.)
Thanks for any help you can provide me!
November 15th, 2012, 09:43 AM
I don't really see the point of making SELECTs on a broken table. Wouldn't it make more sense to repair it (by deleting the unwanted rows) and then put the currently missing UNIQUE index on the table?
Anyway, if I understand you correctly, you want to GROUP the table by the facet_id and then select the MAX(faceted_item_id) to get the highest item ID for each facet.
November 15th, 2012, 10:47 AM
your table design, which is unfortunately all too common, has an auto_increment (surrogate) primary key but lacks a unique (natural) key
Originally Posted by dragonflyeye
once you clean up the data, declare a UNIQUE index on the column(s) that should be unique
November 15th, 2012, 10:55 AM
Um, do you never read the previous replies? I already said that, so no need to repeat it.
Originally Posted by r937
November 15th, 2012, 11:58 AM
no, i do
Originally Posted by Jacques1