how to get a distinct value when the columns have different data

Question

Consider the below table

KEY    WO#    Parts   Name
77     11     1       aa
77     11     2       aa
81     12     2       bb
82     9      3       cc

Now i want to write a query for my report so that whenever theres a duplicate RKEY i want it to ignore it even though the number of parts in the duplicate RKEY are different and proceed with the next RKEY. Is this possible

End result

KEY    WO#    Parts   Name
77     11     1       aa
81     12     2       bb
82     9      3       cc

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Tab Alleman, Jul 26 '17 at 19:44
I´m sorry... I didn't realize it wasn't MySQL. Answer deleted! — Renato Tarso, Jul 26 '17 at 19:52

score 2 · Accepted Answer · answered Jul 26 '17 at 19:15

2

This has been asked and answered literally hundreds of times. But it is faster to type out a solution than find an existing answer.

select [KEY]
    , [WO#]
    , Parts
    , Name
from
(
    select [KEY]
        , [WO#]
        , Parts
        , Name
        , RowNum = ROW_NUMBER() over (partition by KEY order by Parts)
    from YourTable
) x
where RowNum = 1

answered Jul 26 '17 at 19:15

Sean Lange

33,028
3
25
40

I live for the day I earn the dupe hammer in Sql-Server. – Tab Alleman Jul 26 '17 at 19:45

score -1 · Answer 2 · edited Jul 27 '17 at 03:01

-1

EatPeanutButter is correct: If you use the 'group by' command on 'Key', only the first instance of 'Key' will be shown in the end result.

select * from table group by Key;

edited Jul 27 '17 at 03:01

BSMP

4,596
8
33
44

answered Jul 26 '17 at 19:25

Anders Palm

31
2

4

This query will not provide the desired results in the question. In fact, it will result in an error. – Tab Alleman Jul 26 '17 at 19:47

how to get a distinct value when the columns have different data

2 Answers2