How to join table to itself and select max values in SQL
- by Jakub Konop
I have a contracts table:
contractId date price partId
1 20120121 10 1
2 20110130 9 1
3 20130101 15 2
4 20110101 20 2
The contract with greatest date being the active contract (don't blame me, I blame infor for creating xpps)
I need to create query to see only active contracts (one contract per part, the contract with highest date).
So the result of the query should be like this:
contractId date price partId
1 20120121 10 1
3 20130101 15 2
I am out of ideas here, I tried self joining the table, I tried aggregation functions, but I can't figure it out. If anyone would have any idea, please share them with me..