I have table like this:
| id | purchase_order_id | version | remarks | status |
|---|---|---|---|---|
| 1 | 24 | 1 | new | edit |
| 2 | 24 | 2 | update | edit |
| 3 | 25 | 1 | new | confirm |
| 4 | 24 | 3 | update | confirm |
my propose is how to select query for first makesure purchase order id it is distinct and then version it is getting the latest version. so the propose data is is like this :
| id | purchase_order_id | version | remarks | status |
|---|---|---|---|---|
| 3 | 25 | 1 | new | confirm |
| 4 | 24 | 3 | update | confirm |
CodePudding user response：
Use DISTINCT ON:
SELECT DISTINCT ON (purchase_order_id) *
FROM yourTable
ORDER BY purchase_order_id, version DESC;
