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.
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 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
)
)