Using Aggregate Functions in Select statements

• Mark Owens

  Valued Member

  Points: 53

  More actions

  November 4, 2004 at 9:51 am

  #59794

  I am trying to determine the most recent receipt of each inventory item in a Purchase Receipts table.  I also want to know the Receipt Number and Unit Cost for each of those most recent receipts.  I have this much:

  select itemnmbr, max(daterecd) 'Receipt Date' from IV10200

  group by itemnmbr

  When I try to add other columns I get error messages saying that the columns must be part of an aggregate function or in the group by clause.

  Can someone please point me in the right direction for getting other columns displayed in the results pane or Query Analyzer?

  Thanks for whatever help you can give,

  Mark Owens

• Adam Machanic

  SSCoach

  Points: 15370

  More actions

  November 4, 2004 at 10:54 am

  #528965

  I think you want something like:

  select *

  from IV10200

  where daterecd =

  (SELECT max(daterecd)

  from IV10200 i1

  where i1.itemnmbr = IV10200.itemnmbr)

  --
  Adam Machanic
  whoisactive

• Mark Owens

  Valued Member

  Points: 53

  More actions

  November 4, 2004 at 11:12 am

  #528967

  Thank you very much, Adam.  Using your ideas here is the statement that works for me:

  Select * from IV10200 b where b.daterecd = (select max(a.daterecd) from IV10200 a

  where a.itemnmbr = b.itemnmbr) order by b.itemnmbr

  Thanks again,

  Mark Owens