Luci Coanda Luci Coanda - 6 months ago 10
SQL Question

select multiple columns with max from one column and distinct on another

So I want to select all the records with distinct contractNo and the max versionNo.

So I have a table like this:

| username | contractNo | versionNo |
|===================================|
| lucian | 1 | 1 |
| john | 2 | 1 |
| lucian | 2 | 1 |
| kris | 3 | 1 |
| lucian | 1 | 2 |
| david | 4 | 1 |
| lucian | 1 | 4 |
| adam | 5 | 1 |
| lucian | 2 | 2 |
| kris | 3 | 2 |
| lucian | 3 | 1 |
| lucian | 1 | 3 |
| lucian | 1 | 5 |
| lucian | 4 | 1 |


and I want to select the following records:

| username | contractNo | versionNo |
|===================================|
| lucian | 1 | 5 |
| lucian | 2 | 2 |
| lucian | 3 | 1 |
| lucian | 4 | 1 |


I have this query, however this only returns them in descending order ordered by contractNo and versionNo.

SELECT username, contractNo, versionNo

FROM contracts

WHERE useremail = 'lucian'

order by contractNo, versionNo desc;


I believe that I need to do some kind of join, but I am not sure how.

Any help would be appreciated.

Answer

This should work:

SELECT
    username
    ,contractNo
    ,MAX(versionNo) AS versionNo
FROM contracts
WHERE username = 'lucian'
GROUP BY
    username
    ,contractNo
ORDER BY contractNo ASC

Enjoy.