CODEWITHSUNDEEP
sqloracle-database
Venkateswararao Sunkara
Venkateswararao Sunkara

Reputation: 31

Find the TOP 400 rows having the most relationships

The table looks like this

RELATION_ID  OBJECT_ID  RELATED_TO_OBJECT_ID    CLASS_ID    RELATION_TYPE_ID
7966561       910675              69305               7             1
7966562       910675              439104              7             1

The OBJECT_ID value 910675 has two related OBJECT_ID values in RELATED_TO_OBJECT_ID. I would like to find the top 400 OBJECT_IDs having the most RELATED_TO_OBJECT_ID records and also where CLASS_ID = 7 and RELATION_TYPE_ID = 1.

Upvotes: 1

Views: 296

Answers (2)

Ken Y-N
Ken Y-N

Reputation: 15018

You need a query along these lines, I think:

SELECT * FROM (
    SELECT OBJECT_ID, COUNT(*) as objectcount
    FROM table
    WHERE CLASS_ID = 7 AND RELATION_TYPE_ID = 1
    GROUP BY OBJECT_ID ORDER BY objectcount DESC)
 WHERE ROWNUM <= 400

The inner query creates a sorted table of OBJECT_IDs and the number of them that appear, then the outer query takes that result and limits it to the first 400 rows.

Note, I don't have an Oracle setup, but I referred to this previous question.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

If you are on Oracle 12c, you could use the new Top-n row limiting feature.

SELECT OBJECT_ID,
  COUNT(*) cnt
FROM table_name
WHERE CLASS_ID       = 7
AND RELATION_TYPE_ID = 1
GROUP BY OBJECT_ID
ORDER BY cnt DESC
FETCH FIRST 400 ROWS ONLY

If you want to include the duplicates, then add WITH TIES.

SELECT OBJECT_ID,
  COUNT(*) cnt
FROM table_name
WHERE CLASS_ID       = 7
AND RELATION_TYPE_ID = 1
GROUP BY OBJECT_ID
ORDER BY cnt DESC
FETCH FIRST 400 ROWS WITH TIES

Upvotes: 0

Related Questions