How to join table to itself and select max values in SQL

Posted by Jakub Konop on Stack Overflow See other posts from Stack Overflow or by Jakub Konop
Published on 2012-11-29T17:00:38Z Indexed on 2012/11/29 17:03 UTC
Read the original article Hit count: 192

Filed under:
|

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..

© Stack Overflow or respective owner

Related posts about sql

Related posts about query