Getting distinct values with the highest value in a specific column

Question

How can I get the highlighted rows from the table below in SQL? (Distinct rows based on User name with the highest Version are highlighted)

In case you need plain text table:

+----+-----------+---+
| 1  | John      | 1 |
+----+-----------+---+
| 2  | Brad      | 1 |
+----+-----------+---+
| 3  | Brad      | 3 |
+----+-----------+---+
| 4  | Brad      | 2 |
+----+-----------+---+
| 5  | Jenny     | 1 |
+----+-----------+---+
| 6  | Jenny     | 2 |
+----+-----------+---+
| 7  | Nick      | 4 |
+----+-----------+---+
| 8  | Nick      | 1 |
+----+-----------+---+
| 9  | Nick      | 3 |
+----+-----------+---+
| 10 | Nick      | 2 |
+----+-----------+---+
| 11 | Chris     | 1 |
+----+-----------+---+
| 12 | Nicole    | 2 |
+----+-----------+---+
| 13 | Nicole    | 1 |
+----+-----------+---+
| 14 | James     | 1 |
+----+-----------+---+
| 15 | Christine | 1 |
+----+-----------+---+

What I have so far is (works for one user)

SELECT USER, VERSION 
FROM TABLE 
WHERE USER = 'Brad' 
AND VERSION  = (SELECT MAX(VERSION ) FROM TABLE WHERE USER= 'Brad')

Answer 1

SELECT USER, max(VERSION) VERSION
FROM TABLE GROUP BY USER;

If you need an ID then

SELECT ID, USER, VERSION FROM (
    SELECT ID, USER, VERSION, 
           RANK() OVER(PARTITION BY USER ORDER BY VERSION DESC) RNK 
    FROM TABLE
) WHERE RNK = 1;

if you have

| 2  | Brad      | 5 |
+----+-----------+---+
| 3  | Brad      | 3 |
+----+-----------+---+
| 4  | Brad      | 5 |

The query with RANK gives you both users

| 2  | Brad      | 5 |
+----+-----------+---+
| 4  | Brad      | 5 |

If you need only one row then replace RANK() with ROW_NUMBER()

In your query you're using AND VERSION = (SELECT MAX(VERSION ) FROM TABLE WHERE USER= 'Brad') which is equivalent to RANK() (all rows with the max VERSION)

Answer 2

The first_value analytic function should do the trick:

SELECT DISTINCT FIRST_VALUE (id) 
                   OVER (PARTITION BY name ORDER BY version DESC)
       name,
       FIRST_VALUE (version) 
                   OVER (PARTITION BY name ORDER BY version DESC)
FROM   my_table

Another way to go would be to use the row_number function:

SELECT id, name, version
FROM   (SELECT id, name, version
               ROW_NUMBER() OVER (PARTITION BY name ORDER BY version DESC) rn
        FROM   my_table)
WHERE   rn = 1

Not sure which I prefer, personally. They each have their merit and their ugliness.