CODEWITHSUNDEEP
sqloracle-databaseselectdistinct
Ned
Ned

Reputation: 1207

Getting distinct values with the highest value in a specific column

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)

enter image description here

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')

Upvotes: 3

Views: 6188

Answers (3)

Multisync
Multisync

Reputation: 8787

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)

Upvotes: 5

Deep
Deep

Reputation: 3202

this might help you :

select id, user, version 
from 
(
   select id, user, version, row_number() over (partition by user order by version desc) rownum
   from yourtable
) as t
where t.rownum = 1

sql fiddle

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

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.

Upvotes: 2

Related Questions