This discussion is archived
11 Replies Latest reply: Oct 17, 2012 3:50 PM by Dhar RSS

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

965733 Newbie
Currently Being Moderated
Hello,

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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points