CODEWITHSUNDEEP
sqlgroup-by
user2893780
user2893780

Reputation: 121

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-05    55555
10002   124         2015-02-01     4545

The result i want

itemid inventdimid datephysical transrefid
10001   123           2015-02-05   555
10002   123           2015-01-03    3566
 10002   124         2015-02-01     4545

MY query:

SELECT a.itemid,a.inventdimid,max(a.datephysical),a.transrefid
  FROM  a where dataareaid = 'ermi' 
group by a.itemid,a.inventdimid

it is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 69

Answers (4)

Necreaux
Necreaux

Reputation: 9776

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' 
group by 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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

Use the ANSI standard row_number() function:

select t.*
from (select t.*,
             row_number() over (partition by itemid, inventdimid
                                order by datephysical desc) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 2

Raging Bull
Raging Bull

Reputation: 18737

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 
         GROUP BY itemid,inventdimid) T1 JOIN
(SELECT itemid,inventdimid,datephysical,transrefid
 FROM TableName) T2 ON T1.itemid=T2.itemid AND T1.inventdimid=T2.inventdimid

Upvotes: 0

jarlh
jarlh

Reputation: 44746

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 = (select max(datephysical)
                      from a a2
                      where a1.itemid = a2.itemid
                        and a1.inventdimid = a2.inventdimid
                        and a2.dataareaid = 'ermi')

Upvotes: 0

Related Questions