Faced the problem of choosing the n th number of records. Let me explain: you need to select 10 rows for each result in the table. You can, of course, use Union , but the results are about 1000.

 select * from t_1 x where x.result = '1' and rownum <= 10 union select * from t_1 x where x.result = '2' and rownum <= 10 union select * from t_1 x where x.result = '3' and rownum <= 10 union select * from t_1 x where x.result = '4' and rownum <= 10

Question how to write without Union? The Result field has about 1000 unique values.

  • one
    Examples of tables, code? Where is the problem? What did you fail? - JVic
  • select * from t_1 x where x.result = '1' and rownum <= 10 union select * from t_1 x where x.result = '2' and rownum <= 10 union select * from t_1 x where x.result = '3 'and rownum <= 10 union select * from t_1 x where x.result =' 4 'and rownum <= 10 - Gaspar
  • Question how to write without Union? The Result field has about 1000 unique values. - Gaspar
  • And what values ​​are there in which order of recording, i.e. The first 10 pieces per group. And please provide clarifications in the text of the question (link edit question), and not in the comments - Mike

1 answer 1

 select * from ( select A.*, row_number() over(partition by result order by null) RN from table A ) where RN<=10

In the order by clause instead of null should specify the correct sorting order to determine which 10 records from the set to show

  • Thank! Exactly what is needed. I apologize for the curve question) - Gaspar