MAX function no pulling max date

Transact-SQL
1

Just trying to pull records from a single table with multple change reocrds per item. I want the last change. Query pulls all records.

Query
Select productkey, productcostcode, NewCostPrice, max(StartDate) from IC_ProductCostPriceChanges
where productkey = '4080'
group by productkey, productcostcode, NewCostPrice

Results
productkey productcostcode NewCostPrice Date
4080 7 0.0001 8/4/2015 0:00
4080 7 10.549 8/13/2015 0:00

Have tried in Access as well same results.
Thanks

2

Try this:

select productkey
      ,productcostcode
      ,newcostprice
      ,startdate
  from (select productkey
              ,productcostcode
              ,newcostprice
              ,startdate
              ,row_number() over(partition by productkey
                                             ,productcostcode
                                 order by startdate desc
                                ) as rn
         from ic_productcostpricechanges
         where productkey='4080'
        ) as a
 where rn=1
2 Likes
3

Bless You! it worked.
Can you tell me why I have to do that and can't just use Max?

4

You can use "max", but I like the "row_number" method, because it helps you deal with duplicates.

Say you have a duplicate - with the "max" we can't distinguish the entries - with "row_number" we can.

Also you original query didn't work because you grouped by newcostprice.

Using "max" would look something like:

select b.productkey
      ,b.productcostcode
      ,b.newcostprice
      ,b.startdate
  from (select productkey
              ,productcostcode
              ,max(startdate) as startdate
         from ic_productcostpricechanges
         where productkey='4080'
       ) as a
       inner join ic_productcostpricechanges as b
               on b.productkey=a.productkey
              and b.productcostcode=a.productcostcode
              and b.startdate=a.startdate
1 Like