Sql Group By And Max And Other Values

i have a table that contains: itemid inventdimid datephysical transrefid 10001 123 2015-01-02 300002 10002 123 2015-01-03 3566 10001 123 2015-02-

Solution 1:

Use the ANSI standard row_number() function:

select t.*from (select t.*,
             row_number() over (partitionby itemid, inventdimid
                                orderby datephysical desc) as seqnum
      fromtable t
     ) t
where seqnum =1;

Solution 2:

Find max(a.datephysical) for each itemid, inventdimid combination, select all rows from that date.

SELECT itemid, inventdimid, datephysical, transrefid
FROM  a a1
where dataareaid ='ermi'and datephysical = (selectmax(datephysical)
                      from a a2
                      where a1.itemid = a2.itemid
                        and a1.inventdimid = a2.inventdimid
                        and a2.dataareaid ='ermi')         

Solution 3:

You have to create a temporary table with your GROUP BY and then join the original table with it.

Try this:

SELECT T1.*,T2.datephysical,T2.transrefid FROM 
(SELECT itemid,inventdimid 
         FROM TableName 
         GROUPBY itemid,inventdimid) T1 JOIN
(SELECT itemid,inventdimid,datephysical,transrefid
 FROM TableName) T2 ON T1.itemid=T2.itemid AND T1.inventdimid=T2.inventdimid

Solution 4:

I'm assuming you want the transrefid corresponding with the a.datephysical shown? This would be done by turning the column into a subquery:

SELECT a.itemid,a.inventdimid,max(a.datephysical),
(SELECT b.transrefid FROM MY_TABLE b where
b.datareaid ='ermi'and b.itemid = a.itemid and b.inventdimid = a.itemid
and b.datephysical =max(a.datephysical)) as transrefid
FROM MY_TABLE a where dataareaid ='ermi'groupby a.itemid, a.inventdimid

Some databases may not support this syntax though and it will fail if there are more than one records with the same date.