Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nissa.

Asked: October 17, 2017 - 6:02 pm UTC

Last updated: October 24, 2017 - 1:42 am UTC

Version: G

Viewed 1000+ times

You Asked

Hello,

I'd like to remove duplicates from ma table let say V1 is the key column. the key should be max 5 digits as a length

Example:
V1

01254T
50300
50300W
45662

Here I should keep the first one (it has no duplicates) , 50300 and the last one.

I tried this querry but doesn't work

SELECT tab.*
FROM mytable tab
join (
select substr(V1, 1, 5) as tab1, min(length(V1))
FROM tab
group by substr(V1, 1, 5)
) tab1
on substr(corp.V1, 1, 5) = tab1.subb;

Can you help please?

Thank you

Mila






and Connor said...

SQL> create table t ( x varchar2(10));

Table created.

SQL>
SQL> insert into t values ('01254T');

1 row created.

SQL> insert into t values ('50300');

1 row created.

SQL> insert into t values ('50300W');

1 row created.

SQL> insert into t values ('45662');

1 row created.

SQL>
SQL> select x,
  2    count(*) over ( partition by substr(x,1,5)) as c
  3  from t;

X                   C
---------- ----------
01254T              1
45662               1
50300W              2
50300               2

4 rows selected.

SQL>
SQL> select *
  2  from (
  3  select x,
  4    count(*) over ( partition by substr(x,1,5)) as c
  5  from t
  6  )
  7  where c = 1;

X                   C
---------- ----------
01254T              1
45662               1

2 rows selected.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

plus delete

Racer I., October 18, 2017 - 10:51 am UTC

Hi,

delete from t where rowid NOT IN (
  select min(rowid) keep (dense_rank first order by rowid) row_id
  from t
  group by substr(x,1,5))

or : order by x, rowid
or : order by length(x), rowid


Chris Saxon
October 18, 2017 - 3:07 pm UTC

Yep. But why the need for the keep clause?

Why the keep?

A reader, October 23, 2017 - 12:09 pm UTC

I believe the original poster is trying to keep every first occurrence of substr(V1, 1, 5). He wants the result from his example to be:

01254T
50300
45662

not:

01254T
45662
Connor McDonald
October 24, 2017 - 1:42 am UTC

Fair point - I didnt read that from his question but a KEEP or substituting my "count" with "row_number" will do the trick
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.