CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
GagakAngkasa
GagakAngkasa

Reputation: 39

Get latest date from table with their line in mysql

I have 2 tables: Asset and Inventory.

Table Asset

enter image description here

Table Inventory

enter image description here

When I run the following mysql query:

SELECT a.assetnum,a.description,a.user,MAX(b.invdate) as invdate , b.note 
FROM asset a 
LEFT JOIN inventory b on a.assetnum=b.assetnum 
GROUP BY a.assetnum, a.description

I get is this:

enter image description here

but, it should be like this....

enter image description here

Whats wrong with my mysql query? Can somebody help me...

Upvotes: 0

Views: 93

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

One alternative to support your try and good to try it also:

SELECT a.assetnum,
    MAX(a.description) AS description,
    MAX(a.user) AS user,
    MAX(b.invdate) as invdate, 
    (SELECT i.note FROM inventory i 
     WHERE i.invdate = MAX(b.invdate) AND i.assetnum=a.assetnum) note
FROM asset a 
LEFT JOIN inventory b on a.assetnum=b.assetnum 
GROUP BY a.assetnum

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10711

You basicaly get a random note in the result. You probably need something like this:

 select a.assetnum,a.description,a.model, i.invdate, i.note 
 from inventory i
 join
 (
   select assetnum, max(invdate) max_invdate
   from inventory
   group by assetnum
 ) t on i.assetnum = t.assetnum and
        i.invdate = t.max_invdate
 right join asset a on a.assetnum = i.assetnum

Upvotes: 1

Related Questions