Select one of each column value based on the maximum value of another column

Question

I need to return a record of each prod_numb and the cost_amt based on the most recent sale_date column.

Basically I need something like this:

SELECT prod_numb  
     , cost_amt 
  FROM product_cost 
 WHERE MAX(sale_date)

I know this will not run, but its best way to describe it. Can someone help with this query?

Array
(
[0] => Array
    (
        [prod_numb] => 2336
        [cost_amt] => 32.00
        [sale_date] => 2015-01-01
    )

[1] => Array
    (
        [prod_numb] => 2336
        [cost_amt] => 32.00
        [sale_date] => 2015-01-02
    )

[2] => Array
    (
        [prod_numb] => 2458
        [cost_amt] => 32.00
        [sale_date] => 2015-01-03
    )

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, Aug 02 '18 at 16:59

score 1 · Answer 1 · answered Aug 02 '18 at 16:40

1

you could use a inner join with the subselect grouped by prod_num for max date

select  prod_numb, cost_amt 
FROM product_cost 
inner join (
  select prod_numb, MAX(sale_date) max_date 
  FROM product_cost 
  group by prod_numb
) t on t.prod_numb = product_cost.prod_numb and t.max_date  = product_cost.sale_date

answered Aug 02 '18 at 16:40

ScaisEdge

131,976
10
91
107

sir you missing as alias from query MAX(sale_date) as max_date – Mohit Kumar Aug 02 '18 at 16:58
@MohitKumar .. in mysql the 'AS' in not mandatory .. .. explain better – ScaisEdge Aug 02 '18 at 16:59

krzysztor · Answer 2 · 2018-08-02T17:49:32.090

0

I think you can try something like this:-

SELECT prod_numb, cost_amt 
   FROM product_cost WHERE 
sale_date = (SELECT max(sale_date) FROM product_cost);

edited Aug 02 '18 at 17:49

answered Aug 02 '18 at 16:52

krzysztor

21
5

This only returns one record with the max date. I need to return each prod_numb and cost_amt based on the max date. – Thomas Grauer Aug 02 '18 at 16:56
You're right, now i see that second option don't meet your requirement, but what about first one. It should return every record with max (sale_date) – krzysztor Aug 02 '18 at 17:49

Thomas Grauer · Answer 3 · 2018-08-02T19:35:35.990

0

Thanks guys but I figured it out.

 SELECT pm.prod_numb, pm.long_desc, pm.price_amt, tp.location_id, tp.adult_child_flg, pc.cost_amt FROM product_master pm INNER JOIN ticket_product tp ON pm.prod_numb = tp.prod_numb LEFT OUTER JOIN product_cost pc ON pc.prod_numb = pm.prod_numb AND pc.sale_date = TODAY WHERE pm.status = 'A'

edited Aug 02 '18 at 19:35

answered Aug 02 '18 at 19:04

Thomas Grauer

140
10

Select one of each column value based on the maximum value of another column

3 Answers3