0

Each record contains RecordID, TypeID, GroupID.
There are many records to a Type and many types to a Group.
I want to return 3 records for each combination of type and group.

Any suggestions?

simplified output like this

Group | Type | Record
------+------+--------
1     | 1    | 1
1     | 1    | 2
1     | 1    | 3
1     | 2    | 1
1     | 2    | 2
1     | 2    | 3
1     | 3    | 1
....
9     | 1    | 1
9     | 2    | 2
etc..

This is the solution that worked for me, a variation of the answer given.

It is code for a migration, so absolute optimisation is not necessary - but appreciate further pointers.

SELECT *
FROM yourTable t1
WHERE EXISTS
  (SELECT RecordId
    FROM (
      SELECT RecordId, ROW_NUMBER() OVER (PARTITION BY GroupId, TypeId ORDER BY RecordId) As seq FROM yourTable) t2
      WHERE seq <= 3 AND t2.RecordId = t1.RecordId
    )
ORDER BY GroupId, TypeId
Daniel Cook
  • 1,033
  • 1
  • 9
  • 19
  • Short of doing some loops, I've been reading up on 'possible solutions' - some here http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group - a lot of overcomplicated looking queries and terms i'm unfamiliar with – Daniel Cook Jul 13 '15 at 10:46

1 Answers1

1

Try something like this:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY GroupId, TypeId ORDER BY RecordId) As seq
    FROM yourTable) dt
WHERE seq <= 3
shA.t
  • 16,580
  • 5
  • 54
  • 111
  • Thanks, this looks clean - I will try to troubleshoot it. Error at Command Line : 3 Column : 13 Error report - SQL Error: ORA-00923: FROM keyword not found where expected – Daniel Cook Jul 13 '15 at 10:50
  • This query runs fine if I replace the * for some actual fields - I could use a select * from table where IN those results - or something, will post when I get it. Thanks – Daniel Cook Jul 13 '15 at 11:11
  • I added the final solution to my original post, thank you for getting me there. – Daniel Cook Jul 13 '15 at 11:26