CODEWITHSUNDEEP
sql-serverdatabaserelational-database
Green
Green

Reputation: 111

SQL Server selecting minimum value from duplicates

From the below table how can I pull the minimum value from CODE column for each duplicated USERID.

  USER_ID   |   CODE    | ROW_ID  | NAME
1111111111  |   -0.118  |   1     |  USER1
1111111111  |   91.528  |   2     |  USER2
2222222222  |   92.41   |   3     |  USER3
2222222222  |   10.85   |   4     |  USER4
2222222222  |   56.02   |   5     |  USER5
3333333333  |   -0.324  |   6     |  USER6
3333333333  |   12.78   |   7     |  USER7
4444444444  |   0.0002  |   8     |  USER8
4444444444  |   -1.324  |   9     |  USER9
5555555555  |   93.598  |   10    |  USER11
5555555555  |   101.35  |   11    |  USER12
5555555555  |   -5.425  |   12    |  USER13

I tried the below query, but getting only the USER_ID and MIN(CODE). How to get the entire row as below said output?

SELECT USER_ID, min(CODE) minCODE 
FROM TABLE1 
GROUP BY USER_ID

The output should be:

  USER_ID   |    CODE   | ROW_ID | NAME
1111111111  |   -0.118  |   1    | USER1
2222222222  |   10.85   |   4    | USER4
3333333333  |   -0.324  |   6    | USER6
4444444444  |   -1.324  |   9    | USER9
5555555555  |   -5.425  |   12   | USER13

Upvotes: 1

Views: 3077

Answers (3)

Jahirul Islam Bhuiyan
Jahirul Islam Bhuiyan

Reputation: 799

try this

;with a as ( 
    SELECT 
        * 
       ,ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY CODE) r
    FROM TABLE1 
)
SELECT *
FROM a
WHERE r = 1

Upvotes: 1

durbnpoisn
durbnpoisn

Reputation: 4669

You'd need to run another pass with your initial query as a derived table:

select 
    USER_ID, CODE, ROW_ID, NAME 
from 
    TABLE1 
inner join 
    (SELECT USER_ID, min(CODE) minCODE 
     FROM TABLE1 
     GROUP BY USER_ID) derived TABLE1.USER_ID on derived.USER_ID

This way, you'll get your MIN, then you use that to grab the rest of the data from the table.

Upvotes: 0

Fabio
Fabio

Reputation: 32445

You are almost there, use result from your query to get another columns data

;WITH minvalue AS
(
    SELECT USER_ID
    , MIN(CODE) AS MinCode ¨
    FROM TABLE1 
    GROUP BY USER_ID
)
SELECT t.USER_ID
, t.CODE
, t.ROW_ID
, t.NAME 
FROM TABLE1 t
INNER JOIN minvalue mv ON mv.USER_ID = t.USER_ID
AND mv.MinCode = t.CODE

Upvotes: 0

Related Questions