Comparing products sold by several merchants My application is for the use of comparing a product sold by several merchants on a daily basis. A process runs to load the current values of data for each store and enters it into a database along with any previous data. Later I plan to create graphs and such off of the history of data.

SELECT `a`.`part_number`, GROUP_CONCAT(`a`.`seller_id` ORDER BY `price` ASC), GROUP_CONCAT(`b`.`part_id` ORDER BY `price` ASC), GROUP_CONCAT(`b`.`price` ORDER BY `price` ASC)
FROM `parts`
LEFT JOIN (
           SELECT * 
           FROM (
                 SELECT * 
                 FROM `parts_data` AS `tmp_a`
                 ORDER BY `date_added` DESC
                ) AS `tmp_b`
           GROUP BY `part_id`
          ) AS `b`
ON `a`.`part_id` = `b`.`part_id`
GROUP BY `a`.`part_number`


To explain the query a little, I need to select the most recent price information for several part IDs and group them together with a selected part number for comparison. Also, upon concatenation the grouped items are sorted by price to be highest to lowest.

Each part number also can have any number of item IDs associated with it so there is no implication of how many companies are being watched.




part_number |  column1  |  column2  |  column3
++++++++++++++++++++++++++++++++++++++++++++++++
            | seller:A1 | seller:A3 | seller:A6
  153231    | id:x423   | id:x534   | id:x902
            | cost:23   | cost:34   | cost:57
++++++++++++++++++++++++++++++++++++++++++++++++
            | seller:A3 | seller:A1 | seller:A6
  345123    | id:x313   | id:x631   | id:x652
            | cost:78   | cost:86   | cost:99
++++++++++++++++++++++++++++++++++++++++++++++++
            | seller:A5 | seller:A1 |
  231756    | id:x663   | id:x291   |
            | cost:35   | cost:52   |
The ORDER BY inside the inner-most sub-select is pointless - it's going to get thrown out.  Also, you should never rely on MySQL's behaviour of selecting a 'random' value for non-grouped columns - always explicitly specify the transformation that needs to happen on the data.  You should also review what you think your Company columns are, as I am going to pretty much guarantee that the company isn't the same for every cell in the column.


EDIT:

Actually, I was thinking your ORDER BY in the GROUP_CONCAT() was just fine - I have issues with how you're doing your inner-most SELECT... ORDER BY (that you then do a GROUP BY off of, without specifying your transforms).
Assuming that the tuple [parts_data.part_id, parts_data.date] is unique (although, that's the date of what?  Insert, update, your birthday?  Please name more descriptively), here's how I would have structured this (in a system without CTEs and OLAPs):  

SELECT `a`.`part_number`, GROUP_CONCAT(`a`.`seller_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`part_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`price` ORDER BY `b`.`price` ASC)
FROM `parts` as `a`
LEFT JOIN (`parts_data` as `b`
           INNER JOIN (SELECT `part_id`, MAX(`date`) AS `date`
                       FROM `parts_data`
                       GROUP BY `part_id`) as `c`
           ON `b`.`part_id` = `c`.`part_id` 
           AND `b`.`date` = `c`.`date`)
ON `a`.`part_id` = `b`.`part_id`
GROUP BY `a`.`part_number`
ORDER BY `a`.`part_number` 


Please note that I do not in fact have a MySQL instance to test against.  I also don't know if this will be faster for your database (please note we now have a self-join).  However, it's much clearer what you're expecting the system to do, meaning it's easier to maintain.   


EDIT:  

Here's an alternate version, which may be slightly faster.  I don't work with MySQL (and I have some fairly serious hardward backing me up on DB2), so I'm not up on all the ins and outs.

SELECT `a`.`part_number`, GROUP_CONCAT(`a`.`seller_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`part_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`price` ORDER BY `b`.`price` ASC)
FROM `parts` as `a`
LEFT JOIN (SELECT `b`.`part_id`, `b`.`price`
           FROM `parts_data` as `b`
           WHERE NOT EXISTS (SELECT '1'
                             FROM `parts_data` as `c`
                             WHERE `c`.`part_id` = `b`.`part_id`
                             AND `c`.`date` &gt; `b`.`date`)) as `b`
ON `b`.`part_id` = `a`.`part_id`
GROUP BY `a`.`part_number`
ORDER BY `a`.`part_number`


You may want to try removing the ORDER BY a.part_number clause, and see if that helps.