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

[bsuroop]

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.

[ddrozdov]

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

[ddrozdov]

Kishan, are you trying to figure which columns to index?
Is this an index selecivity thingy?

What do you need this for anyways?
d

[dharma]

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 DESC) rx,x, ENAME,
                RANK() OVER (ORDER BY y DESC) ry,y,SAL,
                RANK() OVER (ORDER BY y DESC) rz,z,DNO 
           FROM
              (
               SELECT ENAME, COUNT(ENAME) OVER (PARTITION BY ENAME) x,
                      SAL, COUNT(SAL) OVER (PARTITION BY SAL) y,
                      DNO, COUNT(DNO) OVER (PARTITION BY DNO) z
                 FROM EMP_LDR
              )
       )