Remove Duplicate ids from select query

Question

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.

you want to delete record from existing table or select unique records? — Sushil Mate, Jul 05 '17 at 07:57
You could do something like SELECT DISTINCT ID FROM Tab; to get only the distinct ids, but with different values in POINT the keyword DISTINCT will always return all rows, as they are different in your current query. — Secespitus, Jul 05 '17 at 07:57
@SushilMate No I want delete the records from select query. (logical delete) — Sai Ye Yan Naing Aye, Jul 05 '17 at 08:00
But in SQL rows are un-ordered, there is no _first row_, unless you specify how to order the rows. — jarlh, Jul 05 '17 at 08:00
Possible duplicate of [Removing duplicate rows from table in Oracle](https://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) — J. Chomel, Jul 05 '17 at 08:14

score 4 · Accepted Answer · answered Jul 05 '17 at 08:00

4

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

answered Jul 05 '17 at 08:00

Chanukya

5,833
1
22
36

1

You've been faster than me ;) +1 – Maciej Los Jul 05 '17 at 08:03
Isn't that SQL Server syntax? Will it run on Oracle as well? – jarlh Jul 05 '17 at 08:03
i would add ORDER BY ID; NAME, POINT - as it seems he wants do keep the rows with the maximum Points – Esteban P. Jul 05 '17 at 08:05
1

@jarlh, No, Oracle well knows [ROW_NUMBER](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm) function. Please follow the link. – Maciej Los Jul 05 '17 at 08:11

djmac · Answer 2 · 2017-07-05T08:20:37.973

4

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

edited Jul 05 '17 at 08:20

answered Jul 05 '17 at 08:04

djmac

827
5
11
27

2

OP defined an issue as: `My condition is If all records are the same then take the **first record**.` So, using Max() does not meet criteria. – Maciej Los Jul 05 '17 at 08:12
My understanding is that the GROUP BY takes the first row that has a duplicate and discards any rows that match after it in the result set. So the first version (with no aggregat function) should return the first value. – djmac Jul 05 '17 at 08:20
I would also echo jarlh's comment above - i.e. SQL rows are un-ordered, there is no 'first' row, unless you specify how to order the rows. – djmac Jul 05 '17 at 08:23
@djmac Your first statement is not valid. Columns not in the group by must be in an aggregate. – Magnus Jul 05 '17 at 12:34
@MaciejLos In the comments to the question the ops writes that he wants the results "order by id", which pretty much means a random `POINT` within the group. At least using `Max` would give consistent results. – Magnus Jul 05 '17 at 12:37

score 2 · Answer 3 · answered Jul 05 '17 at 08:17

2

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.

answered Jul 05 '17 at 08:17

J. Chomel

8,193
15
41
69

Remove Duplicate ids from select query

3 Answers3