Determinie the number of duplicate or more value in Oracle table

I need help converting this MS Access query statement into an oracle state to find the number of duplicate records in a field in oracle.
This is the MS Access query, can someone help me convert it to oracle.


SELECT First(s.OBJECT_ID) AS OBJECT_ID, Count(s.OBJECT_ID) AS NumberOfDups
FROM STORAGE_LOCATION s
GROUP BY s.OBJECT_ID
HAVING (((Count(s.OBJECT_ID))>1));
gilnariAsked:
Who is Participating?
 
Tomas Helgi JohannssonCommented:
Hi!

The equivalent of the above Access query is this

using Oracle 12c
SELECT s.object_id AS object_id, COUNT(s.object_id) AS numberofdups
          FROM storage_location s
        GROUP BY s.object_id
        HAVING COUNT(s.object_id) > 1 fetch first 1 rows only

Open in new window


other versions (10G and 11G )

SELECT * FROM (
SELECT s.object_id AS object_id, COUNT(s.object_id) AS numberofdups
          FROM storage_location s
        GROUP BY s.object_id
        HAVING COUNT(s.object_id) > 1 ) WHERE ROWNUM = 1

Open in new window


The Access First function returns the first row in a result set returned from a query.

Regards,
     Tomas Helgi
0
 
sdstuberCommented:
SELECT *
  FROM (SELECT s.object_id AS object_id, COUNT(s.object_id) AS numberofdups
          FROM storage_location s
        GROUP BY s.object_id
        HAVING COUNT(s.object_id) > 1)
 WHERE ROWNUM = 1;


Your use of FIRST on a the group by criteria is a bit strange.  I don't think you're going to get what you want in Access with that query (functionally, it doesn't do anything in the way you're using it)

The query above is my guess at what you're really trying to do.'
The query below is a more direct translation of the actual functionality

SELECT s.object_id AS object_id, COUNT(s.object_id) AS numberofdups
          FROM storage_location s
        GROUP BY s.object_id
        HAVING COUNT(s.object_id) > 1
0
 
gilnariAuthor Commented:
Yup the second one works, we are 11g.   12 is that different  oiy vay.
0
 
sdstuberCommented:
No split?  Isn't the second one the same thing in the first post?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.