11 Replies Latest reply on Oct 17, 2012 10:50 PM by Dhar

    Answers Query - return result based upon max(date) in a different column


      I am using Answers and what should be a simple query has taken up a lot of my time.

      I have the following data structure:

      Buyer Invoice Date Invoice Currency
      JERRY 01/05/2012 USD
      JERRY 01/04/2012 GBP
      JERRY 01/03/2012 GBP
      JERRY 01/02/2012 GBP
      HARRY 01/05/2012 GBP
      HARRY 01/04/2012 GBP
      HARRY 01/03/2012 GBP
      HARRY 01/03/2012 EUR
      HARRY 01/02/2012 EUR

      The above is a list of buyers with invoices for the buyer.

      I am simply trying to find how to pull back the following information:
      Buyer Invoice Currency on the most recent invoice

      So the results should be:
      JERRY 01/05/2012 USD
      HARRY 01/05/2012 GBP

      I have tried a column for buyer, a column (max(invoice date)) and a column for invoice currency.
      The results given are that there are two lines for each buyer returned, one for each currency.
      I get:
      JERRY 01/05/2012 USD
      JERRY 01/05/2012 GBP
      HARRY 01/05/2012 GBP
      HARRY 01/05/2012 EUR

      I understand that this is probably an extremely easy Answers query, but I have spent many hours and the help would greatly be appreciated.

      Thanks in advance.