user2893780 user2893780 -4 years ago 56
SQL Question

SQL group by and max and other fields

I have a table that contains:

ITEMID COSTAMOUNTPOSTED QTY DATEPHYSICAL
10001 20 20 2014-10-01
10001 30 20 2014-10-20
10005 20 20 2014-10-01
10005 20 30 2014-10-15


I want to select the last physical action with the item, the result I want to get is:

ITEMID COSTAMOUNTPOSTED QTY DATEPHYSICAL
10001 30 20 2014-10-20
10005 20 30 2014-10-15


The query I run :

SELECT itemid,costamountposted,qty,datephysical
FROM A
where datephysical =(select max(datephysical)
FROM A


But I only get result with items that have biggest physical date. Any suggestions?

Answer Source

You can do this using subquery,

SELECT a.*
FROM tableName a 
     INNER JOIN
     (
        SELECT ITEMID , MAX(DATEPHYSICAL) max_date
        FROM tableName 
        GROUP BY ITEMID 
     ) b ON a.ITEMID  = b.ITEMID AND a.DATEPHYSICAL = b.max_date
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download