CODEWITHSUNDEEP
mysqlsqlmax
Massimo
Massimo

Reputation: 966

How to get rows with max date when grouping in MySQL?

I have a table with prices and dates on product:

id
product
price
date

I create a new record when price change. And I have a table like this:

id product price date
1  1       10    2014-01-01
2  1       20    2014-02-17
3  1        5    2014-03-28
4  2       25    2014-01-05
5  2       12    2014-02-08
6  2       30    2014-03-12

I want to get last price for all products. But when I group with "product", I can't get a price from a row with maximum date.

I can use MAX(), MIN() or COUNT() function in request, but I need a result based on other value.

I want something like this in final:

product price date
1        5    2014-03-28
2       30    2014-03-12

But I don't know how. May be like this:

SELECT product, {price with max date}, {max date}
FROM table
GROUP BY product

Upvotes: 13

Views: 29108

Answers (4)

Nitesh
Nitesh

Reputation: 177

SELECT
    product,
    price,
    date
FROM
    (SELECT
        product,
        price,
        date
    FROM table_name ORDER BY date DESC) AS t1
GROUP BY product;

Upvotes: -1

John Woo
John Woo

Reputation: 263683

Alternatively, you can have subquery to get the latest get for every product and join the result on the table itself to get the other columns.

SELECT  a.*
FROM    tableName a
        INNER JOIN 
        (
            SELECT  product, MAX(date) mxdate
            FROM    tableName
            GROUP   BY product
        ) b ON a.product = b.product
                AND a.date = b.mxdate

Upvotes: 20

fthiella
fthiella

Reputation: 49039

You can use a subquery that groups by product and return the maximum date for every product, and join this subquery back to the products table:

SELECT
  p.product,
  p.price,
  p.date
FROM
  products p INNER JOIN (
    SELECT
      product,
      MAX(date) AS max_date
    FROM
      products
    GROUP BY
      product) m
  ON p.product = m.product AND p.date = m.max_date

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think the easiest way is a substring_index()/group_concat() trick:

SELECT product,
       substring_index(group_concat(price order by date desc), ',', 1) as PriceOnMaxDate
       max(date)
FROM table
GROUP BY product;

Another way, that might be more efficient than a group by is:

select p.*
from table t
where not exists (select 1
                  from table t2
                  where t2.product = t.product and
                        t2.date > t.date
                 );

This says: "Get me all rows from the table where the same product does not have a larger date." That is a fancy way of saying "get me the row with the maximum date for each product."

Note that there is a subtle difference: the second form will return all rows that on the maximum date, if there are duplicates.

Also, for performance an index on table(product, date) is recommended.

Upvotes: 8

Related Questions