Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have some tables:

ws_shop_product

CREATE TABLE `ws_shop_product` (
     `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT
     `product_title` varchar(255) COLLATE utf8_general_ci DEFAULT NULL,
 PRIMARY KEY (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14499 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

ws_system_admin

CREATE TABLE `ws_system_admin` (
     `admin_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `admin_username` varchar(255) NOT NULL,
     `admin_password` char(40) NOT NULL,
 PRIMARY KEY (`admin_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

ws_shop_product-updated

CREATE TABLE `ws_shop_product-updated` (
     `updated_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `updated_product` int(10) unsigned DEFAULT NULL,
     `updated_admin` int(10) unsigned DEFAULT NULL,
     `updated_date` datetime DEFAULT NULL,
 PRIMARY KEY (`updated_id`),
 KEY `updated_product` (`updated_product`),
 KEY `updated_admin` (`updated_admin`)
) ENGINE=MyISAM AUTO_INCREMENT=42384 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

Whenever a product has been changed in the CMS, one row will insert into the ws_shop_product-updated which keep the admin ID, product ID and date.

Some data:

product_id    product_title
----------    -------------
         1    iPhone 5     



updated_product    updated_admin    updated_date
---------------    -------------    ------------
              1              301    2013-04-13 00:00:00
              1              302    2013-04-15 00:00:00
              1              303    2013-04-16 00:00:00


Now my question is: How can I fetch products with latest update information?

product_id    product_title    latest_admin_id    latest_date
----------    -------------    ---------------    -----------
         1    iPhone 5         303                2013-04-16 00:00:00
share|improve this question
1  
what have you tried so far? – Trent 13 hours ago
1  
Your question is very well formed, but it is true that it would be even better to show you have tried something first. You should look into the GROUP BY clause as well as the MAX() function. Please check these manual pages, you might find the solution yourself. If not, please come back with what you have tried. – YaK 12 hours ago

3 Answers

up vote 0 down vote accepted

Because you are using mysql, you can use mysql's special (and non-portable) group by functionality to produce this fairly simple query:

SELECT * FROM (
  SELECT p.product_id, p.product_title,
    u.updated_admin latest_admin_id, u.updated_date latest_date
  FROM ws_shop_product p
  LEFT JOIN `ws_shop_product-updated` u ON u.updated_product = p.product_id
  ORDER BY u.updated_date DESC) x
GROUP BY 1

This query will return all products, even if they don't have a row in the "updated" table - returning null values for the "latest" columns when there's no such row.

The reason this works is that (for mysql only) when not all non-aggregated columns are named in the group by, mysql returns the first row found for each unique combination of values of columns named in the group by clause. By ordering the data in the subquery latest-first, the first row found for each product_id will be the latest.

share|improve this answer
+1 PERFECT, with fast result, but really I didn't understand your query, it's very strange to me. I need to improve my mysql/sql, could you give me some start point/book/manual or something else? Thanks :) – user2450111 11 hours ago
up vote 0 down vote

You could use a query like this:

SELECT
  p.product_id,
  p.product_title,
  u.updated_admin latest_admin_id,
  u.updated_date latest_date
FROM
  `ws_shop_product-updated` u INNER JOIN ws_shop_product p
  ON u.updated_product = p.product_id
WHERE
  (u.updated_product, u.updated_date) IN
  (SELECT   updated_product, MAX(updated_date)
   FROM     `ws_shop_product-updated`
   GROUP BY product_id)

Please see fiddle here.

The subquery will return the maximum updated_date for each product, the outer query will return all columns of all rows that have the maximum updated_date for every product.

share|improve this answer
When I run this query in phpMyAdmin, it takes a very looooong time. – user2450111 11 hours ago
up vote -1 down vote

You can try this :

      WITH    CTE ( PRODUCTID, PRODUCT_TITLE )
                  AS ( SELECT   *
                       FROM     dbo.ws_shop_product
                     ),
                CTE2 ( updated_id, updated_product, updated_admin, updated_date, ROW )
                  AS ( SELECT   * ,
                                ROW = ROW_NUMBER() OVER ( PARTITION BY UPDATED_PRODUCT ORDER BY UPDATED_DATE DESC )
                       FROM     dbo.ws_shop_productupdated
                     )
            SELECT  A.* ,
                    B.UPDATED_ADMIN ,
                    UPDATED_DATE
            FROM    CTE A
                    JOIN CTE2 B ON A.PRODUCTID = B.UPDATED_PRODUCT
            WHERE   ROW = 1
share|improve this answer
-1 This is a mysql question. SQL Server syntax is useless. – Bohemian 11 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.