CODEWITHSUNDEEP
sqldb2
Zeus
Zeus

Reputation: 6566

Remove duplicate rows in the following scenario

I have a table with the following values

id   |  desc |   code |     userId       
----------------------------------
12   |   ABC |    005 |     user12 
13   |   ABC |    005 |     user12
14   |   ABC |    005 |     user12
15   |   ABC |    005 |     user12
21   |   XYZ |    005 |     user13 
22   |   XYZ |    005 |     user13
24   |   XYZ |    005 |     user13
25   |   XYZ |    005 |     user13

I'd like to write a SQL to get the distinct (id, desc , code, userId) values from the above table (here the user can get any one of the 4 Ids (among 12, 13, 14,15) for userId user12).

Expected result(ignore the first column as it can by any one of the 4 values)

12   |   ABC |    005 |     user12 
21   |   XYZ |    005 |     user13

Upvotes: 0

Views: 50

Answers (2)

Maciej Dobrowolski
Maciej Dobrowolski

Reputation: 12122

Have you tried SELECT MAX(id) (...) GROUP BY desc , code, userId?

Upvotes: 3

OlleR
OlleR

Reputation: 272

select min(t.id), t.desc, t.code, t.userId 
from myTable t
group by t.desc, t.code, t.userId

Upvotes: 3

Related Questions