Is is possible to join two queries but only match the last result?

[spriteburn]

Hello,

My objective is to join two queries but only take the last match for the query on the right.  I'll try to explain.

I have two queries that look like this:

Query 1

ID | Open State | Open Date
1 | New | 20 Jun 2014
2 | New | 20 Jun 2014
3 | New | 22 Jun 2014
4 | New | 25 Jun 2014

Query 2

ID | Close State | Close Date
1 | Closed | 22 Jun 2014
2 | Closed | 21 Jun 2014
2 | Closed | 23 Jun 2014
3 | Closed | 22 Jun 2014

In this particular case, object with ID "2" has been Closed twice.  My objective is to have a join that will only join the last instance for a particular object.  I would like to see:

Joined Query (outer join)

ID | Open State | Open Date | Close State | Close Date
1 | New | 20 Jun 2014 | Closed | 22 Jun 2014
2 | New | 20 Jun 2014 | Closed | 23 Jun 2014
3 | New | 22 Jun 2014 | Closed | 22 Jun 2014
4 | New | 25 Jun 2014 | |

Instead, I am seeing both matches for the object like this:

ID | Open State | Open Date | Close State | Close Date
1 | New | 20 Jun 2014 | Closed | 22 Jun 2014
2 | New | 20 Jun 2014 | Closed | 21 Jun 2014
2 | New | 20 Jun 2014 | Closed | 23 Jun 2014
3 | New | 22 Jun 2014 | Closed | 22 Jun 2014
4 | New | 25 Jun 2014 | |

Is this possible?  Any feedback would be greatly appreciated!

Regards,

Andrew

[BigChris]

In your Query 2, can't you just use maximum([Close Date]) instead of [Close Date]?

I think that would give you the results you're after...

C

[spriteburn]

Hello Chris,

I tried using "maximum([Close Date])" as per your suggestion but it still gives multiple results.  Does this function work on date fields?

Regards,

Andrew

EDIT: I have also tried using "maximum([Close Date] for [ID])" but that doesn't work either.  To elaborate, I am trying these under the Data Items section of the query.

EDIT2: I did it!  I put "[Close Date] = maximum([Close Date] for [ID])" as a filter in the query and it works!