Answer / mohan

select count(*),column_name from table_name group by
column_name having count(*)>1

Answer / sarath

I don't know the exact intention of the above answers, but I am making mine clear that to find the TOTAL DUPLICATED RECORDS and THEIR COUNT OF REPETITIONS, the Pseudo code is:

Select col1,col2,col3,....,COLn count(*) from <TN>
group by col1,col2,col3,....,COLn having count(*) > 1;

Wish everybody complies with it, its tested.

Answer / a.balakrishna

select * from education where rowid in (select min(rowid)
from ex group by sno having count(*)>1);


100% it is true

Best of luck

9177509010

Answer / bhuvi

use self join and find the intersection

Answer / dev

SELECT * FROM TAB1 A WHERE A.ROWID > ANY
(SELECT B.ROWID FROM TAB2 WHERE A.COL1=B.COL1)

Answer / naresh

select * from emp where sal in(select sal from emp group by
sal having count(sal)>1);

Answer / "d"

select count(*),ss from dd group by
ss having count(*)>1

Answer / selvaraj v

In Oracle 10g :
-----------------

SQL> SELECT * FROM emp;

EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
7500 20
7499 ALLEN SALESMAN 7698 17-DEC-80
1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81
1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 19-APR-87
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87
1100 20

7900 JAMES CLERK 7698 03-DEC-81
950 30
7902 FORD ANALYST 7566 03-DEC-81
3000 20
7934 MILLER CLERK 7782 23-JAN-82
1300 10
1 SELVA S/W ENG 7521 18-DEC-09
15000 1000 10
7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30

16 rows selected.

The emp table contains DUPLICATE empno in this table. that's
2 empno's same. We find that empno's, Using the below query :

SQL> SELECT * FROM EMP
2 WHERE ROWID IN(SELECT MAX(ROWID) FROM EMP
3 GROUP BY EMPNO,ENAME,MGR,SAL,DEPTNO
4 HAVING COUNT(*)>1);

EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30

In this emp table contains empno 7251 is 2 times.

Anyone Check this query. I'm expecting user comments...

Answer / jay k patel

SQL> select * from emp;

ENO ENAME DOJ DESGN ADDRESS BSAL
---------- ------- --------- ------- ------- ----------
1 jay 04-JAN-80 md vapi 50000
2 vijay 04-FEB-75 manager vapi 7000
1 ajay 12-JAN-77 dba vapi 9000
3 rajay 05-MAR-70 cleark vapi 5000
4 sujay 28-JAN-73 dba vapi 6000
5 jayesh 07-APR-74 cleark vapi 8000
7 jaymin 04-JAN-62 analyst vapi 4000
6 jaynis 10-MAY-69 dba vapi 9000
1 jayur 04-JAN-74 manager vapi 4000
12 jaynil 14-DEC-71 cleark vapi 2000

10 rows selected.

SQL> SELECT desgn , count(*)
2 FROM emp
3 GROUP BY Desgn
4 Having count(*) > 1 ;

DESGN COUNT(*)
------- ----------
cleark 3
dba 3
manager 2

SQL>

Answer / kumargvk

select * from education a where rownum>(select max(rownum)
from education b where b.rno=b.rno)

Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

0 Answers  

What is sql not null constraint?

0 Answers  

What is difference between procedure and trigger?

0 Answers  

How to change sql*plus system settings?

0 Answers  

How can you save or place your msg in a table?

0 Answers  

select 10 from dual; y its showing all the rows with 10?

1 Answers  

Does view contain data?

0 Answers  

what is unique key constraint? : Sql dba

0 Answers  

how to include numeric values in sql statements? : Sql dba

0 Answers  

Why are cursors used?

0 Answers  

How can you get sql*loader to commit only at the end of the load file? : aql loader

0 Answers  

Is full outer join same as cross join?

0 Answers