#1
  1. No Profile Picture
    Not a pro, but I try hard!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    156
    Rep Power
    10

    Need help with a quick group by union


    So if I run this query I get all the data as I would expect :

    Code:
    select sku,price,max(date),type from (select sku,price,date,'test1' as type from pricetest union all select sku,price,date,'test2' as type from pricetest2) as q group by sku;
    Code:
    +-------+--------+------------+-------+
    | sku   | price  | max(date)  | type  |
    +-------+--------+------------+-------+
    | 12345 | 10.000 | 2012-06-01 | test1 |
    | 12345 | 11.000 | 2012-07-01 | test1 |
    | 12345 | 12.000 | 2012-08-01 | test1 |
    | 23456 |  9.000 | 2012-06-01 | test1 |
    | 23456 |  8.000 | 2012-07-01 | test1 |
    | 23456 | 10.000 | 2012-09-01 | test1 |
    | 23456 |  6.000 | 2012-10-01 | test2 |
    +-------+--------+------------+-------+
    Now I want to get the price and type of the item with the most recent date (the rows in red above).

    When I run this query :

    Code:
    select sku,price,max(date),type from (select sku,price,date,'test1' as type from pricetest union all select sku,price,date,'test2' as type from pricetest2) as q group by sku;
    I get the right dates, but the wrong price/type.

    Code:
    +-------+--------+------------+-------+
    | sku   | price  | max(date)  | type  |
    +-------+--------+------------+-------+
    | 12345 | 10.000 | 2012-08-01 | test1 |
    | 23456 |  9.000 | 2012-10-01 | test1 |
    +-------+--------+------------+-------+
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Code:
    SELECT pricetest.sku
         , pricetest.price
         , pricetest.date
         , 'test1' AS type 
      FROM ( SELECT MAX(date) AS latest_date1
               FROM pricetest ) AS x1
    INNER
      JOIN pricetest
        ON pricetest.date = x1.latest_date1           
    UNION all 
    
    SELECT pricetest2.sku
         , pricetest2.price
         , pricetest2.date
         , 'test2' AS type 
      FROM ( SELECT MAX(date) AS latest_date2
               FROM pricetest2 ) AS x2
    INNER
      JOIN pricetest2
        ON pricetest2.date = x2.latest_date2

    Comments on this post

    • Daedalus81 agrees : My hero!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Not a pro, but I try hard!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    156
    Rep Power
    10
    Thank you kindly!

IMN logo majestic logo threadwatch logo seochat tools logo