Thread: First item IN

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    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!
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by dragonflyeye
    That's not supposed to be the case, but there are errors in the database that I'm trying to clean up.
    your table design, which is unfortunately all too common, has an auto_increment (surrogate) primary key but lacks a unique (natural) key

    once you clean up the data, declare a UNIQUE index on the column(s) that should be unique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by r937
    once you clean up the data, declare a UNIQUE index on the column(s) that should be unique
    Um, do you never read the previous replies? I already said that, so no need to repeat it.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Jacques1
    Um, do you never read the previous replies?
    no, i do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo