CODEWITHSUNDEEP
sqloracle-databaseunique
SoftwareSavant
SoftwareSavant

Reputation: 9737

Select records from Oracle table where single column is unique

I am trying to figure out a simple and elegant way to return every row and column from a table that is uniquely identified by a certain column.

In other words. Imagine a table has the following columns and rows in it... And I wanted all the unique duplicate ID rows...

id | duplicateID |Name | col1 | col2 | col3

1  |  1          |A    |B     | C    | D
2  |  1          |A    |B     | C    | D
3  |  3          |A    |B     | C    | D
4  |  3          |A    |B     | C    | D
5  |  1          |A    |B     | C    | D
6  |  5          |A    |B     | C    | D
7  |  5          |A    |B     | C    | D
8  |  6          |A    |B     | C    | D

I would want the all the records from ID 1, 3, 6 and 8 returned. Because those are all the unique id's from the duplicate record column. But I would want all the records from those rows as well. Is there a way to do this simply? I image not... So far I have written several convuluted queries with no good results. I am no sql expert I must say.

UPDATE

1  |  1          |A    |B     | C    | D
3  |  3          |A    |B     | C    | D
6  |  5          |A    |B     | C    | D
8  |  6          |A    |B     | C    | D

Would be the sample result I would need. This all for Oracle tables btw.

Upvotes: 1

Views: 7587

Answers (2)

Tim
Tim

Reputation: 8921

Not sure I understand the question but here goes:

         select * from T
         inner join
         (
         select distinct duplicateID from T
         ) as V
         on T.id = V.duplicateID

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

you can use an analytic like

select id, duplicateid, name, col1, col2, col3
  from (select id, duplicateid, name, col1, col2, col3,
               row_number() over (partition by duplicateid order by id) rn
          from your_tab)
 where rn = 1;

fiddle: http://sqlfiddle.com/#!4/c38b1/1

Upvotes: 3

Related Questions