DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Duplicate rows

  1. #1
    Join Date
    Jan 2001
    Posts
    50
    Hi all,
    I've got a problem. Consider a table DEPT
    the table structure is given below
    deptno NUMBER NOT NULL
    dname VARCHAR2(20)
    loc VARChAR2

    consider the content of dept table
    18:06:45 ANDY2> select * from dept;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS KOLKATA
    50 SALES INDIA

    Now what i want a query which displays all the contents but the duplicate entries shd be displayed only once...
    that is the o/p shd be like this

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS KOLKATA
    i've used this query which displays only the non duplicate values
    select dname,deptno from dept where dname in (
    select dname from dept
    group by dname
    having count(dname)=1)

    but i cannot proceed further. kindly help...



    Aniruddha Gupta

  2. #2
    Join Date
    Jan 2002
    Posts
    57
    From your example its not clear what you are trying to do.

    If you are trying to remove duplicate rows then try:
    select distinct * from dept;

    --
    Paul

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Where ever you see join criteria in the following examples between outer and sub selects, put your primary key in:

    delete from TABLEA a
    where rowid not in
    (select min(rowid) from TABLEA b
    where a.col1 = b.col1
    and a.col2 = b.col2);

    --------------------------------------------------------------
    Also try this

    1) To display duplicate records using the following query

    Select col1, col2, count(*) duplicates
    from tablename
    group by col1, col2
    having count(*) > 1;

    2) To delete duplicate record use the following query

    DELETE from tablename a
    WHERE ROWID NOT IN (SELECT MIN(ROWID)
    FROM tablename b
    where a.col1 = b.col1
    and a.col2 = b.col2);

  4. #4
    Join Date
    Jan 2001
    Posts
    50

    duplicate

    well i'll be more clear it is not that i want check duplicates or eemove them ...what i want is consider this

    18:06:45 ANDY2> select * from dept;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS KOLKATA
    50 SALES INDIA

    here deptno 30 and 50 have the same dname "SALES" ...what i want the o/p of my query to be is, out of these row only one row shd appear (ie if there is more than one occurance of dname the o/p of query should give me only single occurance...... and other dname shd appear as they are.)

    ie....that the o/p shd be

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS KOLKATA

    ie as deptno --> 50 dname--> is SALES which is same as deptno-->30 .
    only corresponding row for deptno 30 is shown ..
    hope i am clear enough....




    Aniruddha Gupta

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    select *
    from (select a.*, 
             row_number() over (partition by dname  order by
             deptno) numrow
    from dept a) b
    where b.numrow = 1
    
    select *
    from (select a.*,
             dense_rank() over (partition by dname  order by
             deptno) numrow
    from dept a) b
    where b.numrow = 1
    
    select *
    from (select a.*,
             rank() over (partition by dname  order by
             deptno) numrow
    from dept a) b
    where b.numrow = 1
    up to you, be careful with rank and dense_rank

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If analytical functions are not available to you, use the following:

    SELECT * FROM dept d1 WHERE NOT EXISTS
    (SELECT NULL FROM dept d2
    WHERE d2.dname = d1.dname AND d2.ROWID > d1.ROWID);

    Hopefuly your DNAME column is indexed if this is a large table...

    [Edited by jmodic on 08-14-2002 at 04:37 AM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2001
    Posts
    50

    Duplicate

    Thanx all for your post....jurij,pando it is working now.
    Aniruddha Gupta

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width