CODEWITHSUNDEEP
oracle-databaseoracle12c
Sai Ye Yan Naing Aye
Sai Ye Yan Naing Aye

Reputation: 6738

Remove Duplicate ids from select query

I have the following table

ID     NAME    POINT
100    AAA     100
100    AAA     150
100    AAA     70
200    DDD     100
200    DDD     65
300    FFF     50

I would like to delete duplicate id from this table. I would like to get below 

ID     NAME    POINT
100    AAA     100
200    DDD     100
300    FFF     50

I already used DISTINCT keyword in my select query likes this

SELECT DISTINCT ID,NAME,POINT FROM Tab ORDER BY ID;

It is not ok. I just need one unique id per record.So How to remove duplicate ids from select query? My condition is If all records are the same then take the first record.

Upvotes: 2

Views: 15718

Answers (3)

djmac
djmac

Reputation: 895

I think the easiest option is a to group by statement. That is:

SELECT ID, NAME, POINT GROUP BY ID

You might need to add some other logic, deepening on what POINT value you want to select (e.g. max, min etc). That is:

SELECT ID, NAME, MAX(POINT) GROUP BY ID, NAME

Upvotes: 4

J. Chomel
J. Chomel

Reputation: 8395

Use the rowid pseudocolumn.

DELETE FROM your_table
WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY id, name);

If you have more columns in your PK, add them in the group by list.

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #Table1
    ([ID] int, [NAME] varchar(3), [POINT] int)
;

INSERT INTO #Table1
    ([ID], [NAME], [POINT])
VALUES
    (100, 'AAA', 100),
    (100, 'AAA', 150),
    (100, 'AAA', 70),
    (200, 'DDD', 100),
    (200, 'DDD', 65),
    (300, 'FFF', 50)

SELECT ID,NAME,POINT FROM (SELECT* , ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,NAME ) AS RN FROM #TABLE1)A
WHERE RN=1

output

ID     NAME    POINT
100    AAA     100
200    DDD     100
300    FFF     50

Upvotes: 4

Related Questions