Get top N records grouped by another field

Question

I have an Oracle table with ID, SUBJECT, and PAYLOAD (CLOB). I'd like to get a listing of the TOP 10 records who have the biggest PAYLOAD (LENGTH(PAYLOAD)) grouped by subject. So if I have 10 DISTINCT SUBJECT's in the table, the query should return 100 rows (top 10 per subject).

http://stackoverflow.com/questions/134958/get-top-results-for-each-group-in-oracle — Robert Harvey, Dec 16 '14 at 19:02
I saw that but it doesn't quite work. Need something that evaluates LENGTH of the field, not just an ID. — Domenic D., Dec 16 '14 at 19:11

score 2 · Accepted Answer · answered Dec 16 '14 at 19:04

2

Use row_number():

select t.*
from (select t.*, row_number() over (partition by subject order by length(payload) desc) as seqnum
      from table t
     ) t
where seqnum <= 10;

answered Dec 16 '14 at 19:04

Gordon Linoff

1,242,037
58
646
786

Get top N records grouped by another field

1 Answers1