CODEWITHSUNDEEP
phpmysqlselect
user3117337
user3117337

Reputation: 223

Select a max id on materials per date

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

Upvotes: 0

Views: 54

Answers (2)

user2571870
user2571870

Reputation:

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.

Upvotes: 0

Taryn
Taryn

Reputation: 247840

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.

Upvotes: 2

Related Questions