Find the TOP 400 rows having the most relationships

Question

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.

Please edit the question and provide *relevant* sample data in the question (as a text table) and the desired output. — Gordon Linoff, Jun 05 '15 at 02:45
Just a note, `SELECT TOP 400` is [MySQL-specific](http://stackoverflow.com/a/9150494/1270789). See my answer below for the Oracle version. — Ken Y-N, Jun 05 '15 at 03:22
@KenY-N From 12c onwards, Oracle supports the top-n row limiting feature. — Lalit Kumar B, Jun 05 '15 at 04:29

Ken Y-N · Accepted Answer · 2015-06-05T04:30:40.637

1

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.

edited Jun 05 '15 at 04:30

answered Jun 05 '15 at 02:57

Ken Y-N

14,644
21
71
114

i think you meant to use a <= in the last where clause – karina Jun 05 '15 at 03:07
Thanks, [you are indeed correct](http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html)! – Ken Y-N Jun 05 '15 at 03:19
1

@KenY-N You need to `order by desc`. – Lalit Kumar B Jun 05 '15 at 04:27

score 0 · Answer 2 · answered Jun 05 '15 at 04:27

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

Find the TOP 400 rows having the most relationships

2 Answers2