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

Question

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.

I don't know what that is. I am running my queries through SQL Developer it that's what you were asking.. ? — Lucian Coanda, May 21 '16 at 20:35
Joins are for 2 or more tables, but you only indicate one above. — Alvin Bunk, May 21 '16 at 20:39
Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Bulat, May 21 '16 at 22:07

score 2 · Accepted Answer · answered May 21 '16 at 20:37

2

This should work:

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

Enjoy.

answered May 21 '16 at 20:37

score 0 · Answer 2 · answered May 21 '16 at 20:37

Use in clause subselect and group by

SELECT username, contractNo, versionNo 
FROM contracts 
where  (username, contractNo, versionNo) in 
        (select username, contractNo, max(versionNo) 
            from contracts group by username, contractNo )
and username = 'lucian';

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

2 Answers2