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))
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))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yup the second one works, we are 11g. 12 is that different oiy vay.
No split? Isn't the second one the same thing in the first post?
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