<p>What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?</p>

<p>For example: I have a <code>JOBS</code> table with the column <code>JOB_NUMBER</code>.  How can I find out if I have any duplicate <code>JOB_NUMBER</code>s, and how many times they're duplicated?</p>

[{'answer_id': 59242, 'author': 'Bill the Lizard', 'author_id': 1288, 'author_profile': 'https://Stackoverflow.com/users/1288', 'pm_score': 10, 'selected': True, 'text': '<p>Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.</p>\n<pre><code>SELECT column_name, COUNT(column_name)\nFROM table_name\nGROUP BY column_name\nHAVING COUNT(column_name) &gt; 1;\n</code></pre>\n'}, {'answer_id': 59254, 'author': 'JosephStyons', 'author_id': 672, 'author_profile': 'https://Stackoverflow.com/users/672', 'pm_score': 5, 'selected': False, 'text': '<p>Simplest I can think of:</p>\n\n<pre><code>select job_number, count(*)\nfrom jobs\ngroup by job_number\nhaving count(*) &gt; 1;\n</code></pre>\n'}, {'answer_id': 59261, 'author': 'Andrew', 'author_id': 5662, 'author_profile': 'https://Stackoverflow.com/users/5662', 'pm_score': 3, 'selected': False, 'text': '<p>How about:</p>\n\n<pre><code>SELECT &lt;column&gt;, count(*)\nFROM &lt;table&gt;\nGROUP BY &lt;column&gt; HAVING COUNT(*) &gt; 1;\n</code></pre>\n\n<p>To answer the example above, it would look like:</p>\n\n<pre><code>SELECT job_number, count(*)\nFROM jobs\nGROUP BY job_number HAVING COUNT(*) &gt; 1;\n</code></pre>\n'}, {'answer_id': 59278, 'author': 'agnul', 'author_id': 6069, 'author_profile': 'https://Stackoverflow.com/users/6069', 'pm_score': 2, 'selected': False, 'text': "<p>Doing</p>\n\n<pre><code>select count(j1.job_number), j1.job_number, j1.id, j2.id\nfrom   jobs j1 join jobs j2 on (j1.job_numer = j2.job_number)\nwhere  j1.id != j2.id\ngroup by j1.job_number\n</code></pre>\n\n<p>will give you the duplicated rows' ids.</p>\n"}, {'answer_id': 60437, 'author': 'Grrey', 'author_id': 6155, 'author_profile': 'https://Stackoverflow.com/users/6155', 'pm_score': 6, 'selected': False, 'text': "<p>Another way:</p>\n\n<pre><code>SELECT *\nFROM TABLE A\nWHERE EXISTS (\n  SELECT 1 FROM TABLE\n  WHERE COLUMN_NAME = A.COLUMN_NAME\n  AND ROWID &lt; A.ROWID\n)\n</code></pre>\n\n<p>Works fine (quick enough) when there is index on <code>column_name</code>. And it's better way to delete or update duplicate rows.</p>\n"}, {'answer_id': 60584, 'author': 'Evan', 'author_id': 6277, 'author_profile': 'https://Stackoverflow.com/users/6277', 'pm_score': 4, 'selected': False, 'text': "<p>You don't need to even have the count in the returned columns if you don't need to know the actual number of duplicates. e.g.</p>\n\n<pre><code>SELECT column_name\nFROM table\nGROUP BY column_name\nHAVING COUNT(*) &gt; 1\n</code></pre>\n"}, {'answer_id': 12507836, 'author': 'Jitendra Vispute', 'author_id': 772712, 'author_profile': 'https://Stackoverflow.com/users/772712', 'pm_score': 3, 'selected': False, 'text': '<p>In case where  multiple columns identify unique row (e.g relations table ) there you can use following </p>\n\n<p>Use row id \n  e.g.  emp_dept(empid, deptid, startdate, enddate)\n   suppose empid and deptid are unique and identify row in that case</p>\n\n<pre><code>select oed.empid, count(oed.empid) \nfrom emp_dept oed \nwhere exists ( select * \n               from  emp_dept ied \n                where oed.rowid &lt;&gt; ied.rowid and \n                       ied.empid = oed.empid and \n                      ied.deptid = oed.deptid )  \n        group by oed.empid having count(oed.empid) &gt; 1 order by count(oed.empid);\n</code></pre>\n\n<p>and if such table has primary key then use primary key instead of rowid, e.g id is pk then</p>\n\n<pre><code>select oed.empid, count(oed.empid) \nfrom emp_dept oed \nwhere exists ( select * \n               from  emp_dept ied \n                where oed.id &lt;&gt; ied.id and \n                       ied.empid = oed.empid and \n                      ied.deptid = oed.deptid )  \n        group by oed.empid having count(oed.empid) &gt; 1 order by count(oed.empid);\n</code></pre>\n'}, {'answer_id': 15827573, 'author': 'Wahid Haidari', 'author_id': 2247937, 'author_profile': 'https://Stackoverflow.com/users/2247937', 'pm_score': 2, 'selected': False, 'text': '<pre><code>SELECT   SocialSecurity_Number, Count(*) no_of_rows\nFROM     SocialSecurity \nGROUP BY SocialSecurity_Number\nHAVING   Count(*) &gt; 1\nOrder by Count(*) desc \n</code></pre>\n'}, {'answer_id': 35039925, 'author': 'Stacker', 'author_id': 1348805, 'author_profile': 'https://Stackoverflow.com/users/1348805', 'pm_score': -1, 'selected': False, 'text': '<p>Also u can try something like this to list all duplicate values in  a table say reqitem</p>\n\n<pre><code>SELECT count(poid) \nFROM poitem \nWHERE poid = 50 \nAND rownum &lt; any (SELECT count(*)  FROM poitem WHERE poid = 50) \nGROUP BY poid \nMINUS\nSELECT count(poid) \nFROM poitem \nWHERE poid in (50)\nGROUP BY poid \nHAVING count(poid) &gt; 1;\n</code></pre>\n'}, {'answer_id': 35312175, 'author': 'DoOrDie', 'author_id': 5726548, 'author_profile': 'https://Stackoverflow.com/users/5726548', 'pm_score': 0, 'selected': False, 'text': '<p><strong>1. solution</strong></p>\n\n<pre><code>select * from emp\n    where rowid not in\n    (select max(rowid) from emp group by empno);\n</code></pre>\n'}, {'answer_id': 46905493, 'author': 'J. Chomel', 'author_id': 6019417, 'author_profile': 'https://Stackoverflow.com/users/6019417', 'pm_score': 2, 'selected': False, 'text': '<p>I usually use <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i81407" rel="nofollow noreferrer">Oracle Analytic</a> function <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#i86310" rel="nofollow noreferrer">ROW_NUMBER()</a>.</p>\n<p>Say you want to check the duplicates you have regarding a unique index or primary key built on columns (<code>c1</code>, <code>c2</code>, <code>c3</code>).\nThen you will go this way, bringing up <strong><code>ROWID</code></strong> s of rows where the number of lines brought by <code>ROW_NUMBER()</code> is <code>&gt;1</code>:</p>\n<pre class="lang-sql prettyprint-override"><code>Select *\nFrom Table_With_Duplicates\nWhere Rowid In (Select Rowid\n                  From (Select ROW_NUMBER() Over (\n                                 Partition By c1, c2, c3\n                                 Order By c1, c2, c3\n                               ) nbLines\n                          From Table_With_Duplicates) t2\n                 Where nbLines &gt; 1)\n</code></pre>\n'}, {'answer_id': 48224922, 'author': 'Chaminda Dilshan', 'author_id': 9166453, 'author_profile': 'https://Stackoverflow.com/users/9166453', 'pm_score': 1, 'selected': False, 'text': '<p>Here is an SQL request to do that:</p>\n\n<pre><code>select column_name, count(1)\nfrom table\ngroup by column_name\nhaving count (column_name) &gt; 1;\n</code></pre>\n'}, {'answer_id': 51474054, 'author': 'Parth Kansara', 'author_id': 9952631, 'author_profile': 'https://Stackoverflow.com/users/9952631', 'pm_score': 2, 'selected': False, 'text': '<p>I know its an old thread but this may help some one.</p>\n\n<p>If you need to print other columns of the table while checking for duplicate use below:</p>\n\n<pre><code>select * from table where column_name in\n(select ing.column_name from table ing group by ing.column_name having count(*) &gt; 1)\norder by column_name desc;\n</code></pre>\n\n<p>also can add some additional filters in the where clause if needed.</p>\n'}]
['https://Stackoverflow.com/questions/59232', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5662/']