Select a max id on materials per date

Question

I have my table named sup_mon:

genmat_id    mat_name    size     Stock_balance     date   
1              aluminum   100 m       25            2014-02-27
2              aluminum   100 m       30            2014-02-27
3              steel      100 m       60            2014-02-27
4              aluminum   100 m       30            2014-02-28

Here is my distinct select query:

select distinct mat_name, Stock_balance, date, max(genmat_id) from sup_mon where date = '2014-02-27'

The output was:

 genmat_id    mat_name    size     Stock_balance     date   
      3           steel      100 m       60         2014-02-27

How can I select every unique material name on its highest genmat_id on date? like:

 genmat_id    mat_name    size     Stock_balance     date   
    2              aluminum   100 m       30            2014-02-27
    3              steel      100 m       60            2014-02-27

Answer 1

If you want to return the max(genmat_id) for each mat_name and date, then you could use a subquery to get the final result:

select t1.genmat_id,
  t1.mat_name,
  t1.size,
  t1.stock_balance,
  t1.date
from yourtable t1
inner join
(
  select max(genmat_id) genmat_id, date, mat_name
  from yourtable
  group by date, mat_name
) t2
  on t1.genmat_id = t2.genmat_id
  and t1.date = t2.date
  and t1.mat_name = t2.mat_name
where t1.date = '2014-02-27';

See SQL Fiddle with Demo. The subquery returns the max id for each date and mat_name, then you join this back to your table on the genmat_id, date and mat_name to get the final result.

Answer 2

Something like this should work:

SELECT m1.*
FROM sup_mon m1 LEFT JOIN sup_mon m2
 ON (m1.mat_name = m2.mat_name AND m1.genmat_id < m2.genmat_id )
WHERE m2.genmat_id IS NULL and date = '2014-02-27'

Note: adapted from this example.