Latest Contract Version

[report2000]

I have two tables
Table1:
----------
EmployeeID Employee Name Contract

1 Jim 1
1 Jim 2
2 Tom 3
3 George 4
4 Frank 5
5 Oscar 6


Table2:
-----------

Contract Contract Version Amount

1 1 $20
1 2 $25
1 3 $28
2 1 $31
2 2 $50
3 1 $20
4 1 $23
4 2 $45
5 1 $20
6 1 $51

I like to query this two table in order to get only the Employee Name, distinct (Contract) , Amount for the latest Contract version

 

[carp]

Well, to get the amount of the latest contract version, you could query

SELECT * FROM table2
 WHERE (contract, contract_version) IN
    (SELECT contract, max(contract_version) FROM table2
      GROUP BY contract);

In order to link this to the employees, you could use the above query as an inline view:

SELECT e.employee_name, c.contract, c.amount
  FROM table1 e, 
       (SELECT * FROM table2
         WHERE (contract, contract_version) IN
               (SELECT contract, max(contract_version) 
                  FROM table2
                 GROUP BY contract)
       ) c
 WHERE e.contract = c.contract;

(At least I think this should work - I haven't actually tested it!).