Relative Data from a TABLE

[hashjax]

Hi,



I am trying to get relative data from a table, when I use Min, Max option. What I want to do is get the relative data in Column X with Column Y for which I used Min or Max.

Hopefully this example will better explain:

This is my table:
Name Revision Status Date
A 1 1 1-Jan-10
A 2 0 2-Jan-10
B 1 2 1-Jan-10
B 2 1 2-Jan-10
B 3 3 3-Jan-10
C 1 2 1-Jan-10
C 2 1 2-Jan-10


What I want is :
Unique Name | MAX Revision | Max Status | Date (relateive to Max status selected)

So I should get :
Name Revision Status Date
A 2 1 1-Jan-10
B 3 3 3-Jan-10
C 2 2 1-Jan-10


Is there a way to do this in 1 query? I can do it in multiple query where I select / Join based. I want to know the most efficient way to do this.

regards,
Harsh

[pbaldy]

I typically use 2 queries:

http://www.baldyweb.com/LastValue.htm

You can do the first as a subquery in the second, but I find it harder to view/maintain, and I usually have more than one use for the first query anyway.