Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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));
0
gilnari
Asked:
gilnari
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now