How to find most common duplicated value in a column of a table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to find most common duplicated value in a column of a table?

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    How to find most common duplicated value in a column of a table?

    Hello Friends,

    I have an requirement wherein I have to display the most common duplicate value of a column and also maximum times it is duplicated of a table. This I need with a SQL Query.

    Example -

    Empno Name salary Deptno
    ----------------------------------
    1 KK 2000 10
    2 ss 3000 20
    3 KK 1000 10
    4 WW 1000 10
    5 QQ 1000 10
    6 KK 4000 20
    7 KK 4000 10

    My SQL Query should give me the output as below:


    Column Name Most Common Duplicate Value Max Times
    ------------------------------------------------------
    Name KK 4
    Salary 1000 3
    Deptno 10 5





    Thanks in advance

    Kishan.
    SUROOP B

  2. #2
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    The hardcoded approach.
    Dynamic approach for extracharge only.
    This turned out pretty sloppy.
    Mix of analytic and regular group functions. (Thanks to slimdave pointing out the value of analytic)

    select 'EMPNO' column, empno, cnt from(
    select a.empno, a.cnt, rank() over (order by a.cnt desc) rk
    from
    (select empno, count(*) cnt
    from mytable
    group by empno) a)
    where rk = 1
    union all
    select 'NAME' column, name, cnt from(
    select a.name, a.cnt, rank() over (order by a.cnt desc) rk
    from
    (select name, count(*) cnt
    from mytable
    group by name) a)
    where rk = 1
    union all
    select 'SALARY' column, salary, cnt from(
    select a.salary, a.cnt, rank() over (order by a.cnt desc) rk
    from
    (select salary, count(*) cnt
    from mytable
    group by salary) a)
    where rk = 1

  3. #3
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    Kishan, are you trying to figure which columns to index?
    Is this an index selecivity thingy?

    What do you need this for anyways?
    d

  4. #4
    Join Date
    Dec 2000
    Posts
    138

    a little different solution

    PHP Code:
    SELECT MAX(DECODE(rx,1,x)),MAX(DECODE(rx,1,ENAME)) ,
           
    MAX(DECODE(ry,1,y)),MAX(DECODE(ry,1,SAL)),
           
    MAX(DECODE(rz,1,z)),MAX(DECODE(rz,1,DNO))
      
    FROM
           
    (
             
    SELECT RANK() OVER (ORDER BY x DESCrx,xENAME,
                    
    RANK() OVER (ORDER BY y DESCry,y,SAL,
                    
    RANK() OVER (ORDER BY y DESCrz,z,DNO 
               FROM
                  
    (
                   
    SELECT ENAMECOUNT(ENAMEOVER (PARTITION BY ENAMEx,
                          
    SALCOUNT(SALOVER (PARTITION BY SALy,
                          
    DNOCOUNT(DNOOVER (PARTITION BY DNOz
                     FROM EMP_LDR
                  
    )
           ) 

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