sql - Any way to avoid a union in this Oracle query? -

i have table this:

+-------+--------------+--------------+-------------+-------------+-------------+-------------+ | study | point_number | date_created | condition_a | condition_b | condition_c | condition d | +-------+--------------+--------------+-------------+-------------+-------------+-------------+ |     1 |            1 | 01-01-2001   |           1 |           1 |           0 |           1 | |     1 |            2 | 01-01-2001   |           0 |           1 |           1 |           0 | |     1 |            3 | 01-01-2001   |           0 |           1 |           0 |           0 | +-------+--------------+--------------+-------------+-------------+-------------+-------------+ 

the condition_a, b, c , d used classify data points groups. each unique combination of columns group. each group, want retrieve last 200 rows.

at moment have this:

select * my_table point_number <= 200; 

in order each group do:

select * my_table point_number <= 200 condition_a = 1 , condition_b = 1 , condition_c = 1 , condition d = 1 union select * my_table point_number <= 200 condition_a = 1 , condition_b = 1 , condition_c = 1 , condition d = 0 union all...; 

the problem approach there many, many combinations, , make query flexible possible. how can avoid doing union alls , have query automatically retrieve 200 rows each group?

this should work out need do:

with sample_data (select 1 id, 1 ca, 0 cb dual union                      select 2 id, 1 ca, 1 cb dual union                      select 3 id, 1 ca, 1 cb dual union                      select 4 id, 0 ca, 0 cb dual union                      select 5 id, 0 ca, 1 cb dual union                      select 6 id, 0 ca, 1 cb dual union                      select 7 id, 0 ca, 0 cb dual union                      select 8 id, 1 ca, 0 cb dual union                      select 9 id, 1 ca, 1 cb dual union                      select 10 id, 0 ca, 1 cb dual union                      select 11 id, 0 ca, 0 cb dual union                      select 12 id, 1 ca, 0 cb dual union                      select 13 id, 1 ca, 0 cb dual union                      select 14 id, 0 ca, 1 cb dual union                      select 15 id, 0 ca, 0 cb dual union                      select 16 id, 1 ca, 1 cb dual union                      select 17 id, 0 ca, 0 cb dual) select id,        ca,        cb,        row_number() on (partition ca, cb order id) rn   sample_data;          id         ca         cb         rn ---------- ---------- ---------- ----------          4          0          0          1          7          0          0          2         11          0          0          3         15          0          0          4         17          0          0          5          5          0          1          1          6          0          1          2         10          0          1          3         14          0          1          4          1          1          0          1          8          1          0          2         12          1          0          3         13          1          0          4          2          1          1          1          3          1          1          2          9          1          1          3         16          1          1          4 

basically, need find out row number of each row per each group - job analytic functions, row_number() analytic function.

if you've not come across analytic functions before, they're similar aggregate functions (so can find results across groups, aka "partition by") without collapsing rows. recommend research on this, if aren't familiar them!

anyway, once you've assigned row numbers, can throw outer query around sql filter on row number, eg:

with sample_data (select 1 id, 1 ca, 0 cb dual union                      select 2 id, 1 ca, 1 cb dual union                      select 3 id, 1 ca, 1 cb dual union                      select 4 id, 0 ca, 0 cb dual union                      select 5 id, 0 ca, 1 cb dual union                      select 6 id, 0 ca, 1 cb dual union                      select 7 id, 0 ca, 0 cb dual union                      select 8 id, 1 ca, 0 cb dual union                      select 9 id, 1 ca, 1 cb dual union                      select 10 id, 0 ca, 1 cb dual union                      select 11 id, 0 ca, 0 cb dual union                      select 12 id, 1 ca, 0 cb dual union                      select 13 id, 1 ca, 0 cb dual union                      select 14 id, 0 ca, 1 cb dual union                      select 15 id, 0 ca, 0 cb dual union                      select 16 id, 1 ca, 1 cb dual union                      select 17 id, 0 ca, 0 cb dual),          results (select id,                             ca,                             cb,                             row_number() on (partition ca, cb order id) rn                        sample_data) select *   results  rn <= 3;          id         ca         cb         rn ---------- ---------- ---------- ----------          4          0          0          1          7          0          0          2         11          0          0          3          5          0          1          1          6          0          1          2         10          0          1          3          1          1          0          1          8          1          0          2         12          1          0          3          2          1          1          1          3          1          1          2          9          1          1          3