Есть следующий набор данных:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
select 33 version_id, '' device_group_name from dual union all
select 111, '' from dual union all
select 125, '' from dual union all
select 112, '' from dual union all
select 44, '' from dual union all
select 171, '' from dual union all
select 66, '' from dual union all
select 45, '' from dual union all
select 464, '' from dual union all
select 75, '' from dual union all
select 29, '' from dual union all
select 318, '' from dual union all
select 35, '' from dual union all
select 532, '' from dual union all
select 182, '' from dual union all
select 552, '' from dual union all
select 6, '' from dual union all
select 79, '' from dual union all
select 317, '' from dual union all
select 140, '' from dual union all
select 235, '' from dual union all
select 14, '' from dual union all
select 58, '' from dual union all
select 156, '' from dual union all
select 253, '' from dual union all
select 42, '' from dual union all
select 124, '' from dual union all
select 319, '' from dual union all
select 172, '' from dual union all
select 36, '' from dual union all
select 8, '' from dual union all
select 169, '' from dual union all
select 16, '' from dual union all
select 15, '' from dual union all
select 23, '' from dual union all
select 373, '' from dual union all
select 555, '' from dual union all
select 237, '' from dual union all
select 390, '' from dual union all
select 557, '' from dual union all
select 374, '' from dual union all
select 513, '' from dual union all
select 174, '' from dual union all
select 406, '' from dual union all
select 469, '' from dual union all
select 179, '' from dual union all
select 240, '' from dual union all
select 209, '' from dual union all
select 550, '' from dual union all
select 249, '' from dual union all
select 254, '' from dual union all
select 510, '' from dual union all
select 553, '' from dual union all
select 546, '' from dual union all
select 504, '' from dual union all
select 403, '' from dual union all
select 311, '' from dual union all
select 437, '' from dual union all
select 496, '' from dual union all
select 520, '' from dual union all
select 408, '' from dual union all
select 471, '' from dual union all
select 307, '' from dual union all
select 294, '' from dual union all
select 481, '' from dual union all
select 601, '' from dual union all
select 394, '' from dual union all
select 398, '' from dual union all
select 419, '' from dual union all
select 540, '' from dual union all
select 421, '' from dual union all
select 569, '15й этаж' from dual union all
select 590, '15й этаж' from dual union all
select 378, '' from dual union all
select 598, 'ТестНСИ' from dual union all
select 599, '' from dual union all
select 596, '' from dual union all
select 564, '' from dual union all
select 562, '' from dual union all
select 424, '' from dual union all
select 109, '' from dual union all
select 1, '' from dual union all
select 54, '' from dual union all
select 490, '' from dual union all
select 139, '' from dual union all
select 187, '' from dual union all
select 392, '''FROM USER''' from dual union all
select 392, '6' from dual union all
select 484, 'АПБ' from dual union all
select 368, 'АПБ' from dual union all
select 368, '''FROM USER''' from dual union all
select 368, 'Тестовая группа' from dual union all
select 368, '6' from dual union all
select 368, 'ЙЦу' from dual union all
select 368, 'Тест' from dual union all
select 368, 'Тестирование' from dual union all
select 368, 'Тестирование' from dual union all
select 367, 'test' from dual union all
select 368, 'test' from dual union all
select 377, 'test' from dual union all
select 368, 'Группа для теста' from dual union all
select 376, '' from dual union all
select 608, 'ТестНСИ' from dual union all
select 241, '' from dual union all
select 20, '' from dual union all
select 116, '' from dual union all
select 26, '' from dual union all
select 358, '' from dual union all
select 404, '' from dual union all
select 405, '' from dual union all
select 410, '' from dual union all
select 412, '' from dual union all
select 566, '' from dual union all
select 485, '' from dual union all
select 483, '' from dual union all
select 521, '' from dual union all
select 441, '' from dual union all
select 442, '' from dual union all
select 379, '' from dual union all
select 383, '' from dual
Сортировать это нужно следующим образом: сначала сгруппировать по version_id, и внутри каждой группы отсортировать строки по device_group_name, а затем эти группы нужно отсортировать по первым строкам device_group_name. Т.е. в итоге это должно выглядеть так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
select 368 version_id, '''FROM USER''' device_group_name from dual union all
select 368, 'test' from dual union all
select 368, 'АПБ' from dual union all
select 368, 'Группа для теста' from dual union all
select 368, 'ЙЦу' from dual union all
select 368, 'Тест' from dual union all
select 368, 'Тестирование' from dual union all
select 368, 'Тестирование' from dual union all
select 368, 'Тестовая группа' from dual union all
select 368, '6' from dual union all
select 392, '''FROM USER''' from dual union all
select 392, '6' from dual union all
select 367, 'test' from dual union all
select 377, 'test' from dual union all
select 484, 'АПБ' from dual union all
select 1, '' from dual union all
select 109, '' from dual union all
select 111, '' from dual union all
select 112, '' from dual union all
select 116, '' from dual union all
select 124, '' from dual union all
select 125, '' from dual union all
select 139, '' from dual union all
select 14, '' from dual union all
select 140, '' from dual union all
select 15, '' from dual union all
select 156, '' from dual union all
select 16, '' from dual union all
select 169, '' from dual union all
select 171, '' from dual union all
select 172, '' from dual union all
select 174, '' from dual union all
select 179, '' from dual union all
select 182, '' from dual union all
select 187, '' from dual union all
select 20, '' from dual union all
select 209, '' from dual union all
select 23, '' from dual union all
select 235, '' from dual union all
select 237, '' from dual union all
select 240, '' from dual union all
select 241, '' from dual union all
select 249, '' from dual union all
select 253, '' from dual union all
select 254, '' from dual union all
select 26, '' from dual union all
select 29, '' from dual union all
select 294, '' from dual union all
select 307, '' from dual union all
select 311, '' from dual union all
select 317, '' from dual union all
select 318, '' from dual union all
select 319, '' from dual union all
select 33, '' from dual union all
select 35, '' from dual union all
select 358, '' from dual union all
select 36, '' from dual union all
select 373, '' from dual union all
select 374, '' from dual union all
select 376, '' from dual union all
select 378, '' from dual union all
select 379, '' from dual union all
select 383, '' from dual union all
select 390, '' from dual union all
select 394, '' from dual union all
select 398, '' from dual union all
select 403, '' from dual union all
select 404, '' from dual union all
select 405, '' from dual union all
select 406, '' from dual union all
select 408, '' from dual union all
select 410, '' from dual union all
select 412, '' from dual union all
select 419, '' from dual union all
select 42, '' from dual union all
select 421, '' from dual union all
select 424, '' from dual union all
select 437, '' from dual union all
select 44, '' from dual union all
select 441, '' from dual union all
select 442, '' from dual union all
select 45, '' from dual union all
select 464, '' from dual union all
select 469, '' from dual union all
select 471, '' from dual union all
select 481, '' from dual union all
select 483, '' from dual union all
select 485, '' from dual union all
select 490, '' from dual union all
select 496, '' from dual union all
select 504, '' from dual union all
select 510, '' from dual union all
select 513, '' from dual union all
select 520, '' from dual union all
select 521, '' from dual union all
select 532, '' from dual union all
select 54, '' from dual union all
select 540, '' from dual union all
select 546, '' from dual union all
select 550, '' from dual union all
select 552, '' from dual union all
select 553, '' from dual union all
select 555, '' from dual union all
select 557, '' from dual union all
select 562, '' from dual union all
select 564, '' from dual union all
select 566, '' from dual union all
select 58, '' from dual union all
select 596, '' from dual union all
select 599, '' from dual union all
select 6, '' from dual union all
select 601, '' from dual union all
select 66, '' from dual union all
select 75, '' from dual union all
select 79, '' from dual union all
select 8, '' from dual
Я пробовал это сделать так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
with source_rows0 as (
select 33 version_id, '' device_group_name from dual union all
select 111, '' from dual union all
select 125, '' from dual union all
select 112, '' from dual union all
select 44, '' from dual union all
select 171, '' from dual union all
select 66, '' from dual union all
select 45, '' from dual union all
select 464, '' from dual union all
select 75, '' from dual union all
select 29, '' from dual union all
select 318, '' from dual union all
select 35, '' from dual union all
select 532, '' from dual union all
select 182, '' from dual union all
select 552, '' from dual union all
select 6, '' from dual union all
select 79, '' from dual union all
select 317, '' from dual union all
select 140, '' from dual union all
select 235, '' from dual union all
select 14, '' from dual union all
select 58, '' from dual union all
select 156, '' from dual union all
select 253, '' from dual union all
select 42, '' from dual union all
select 124, '' from dual union all
select 319, '' from dual union all
select 172, '' from dual union all
select 36, '' from dual union all
select 8, '' from dual union all
select 169, '' from dual union all
select 16, '' from dual union all
select 15, '' from dual union all
select 23, '' from dual union all
select 373, '' from dual union all
select 555, '' from dual union all
select 237, '' from dual union all
select 390, '' from dual union all
select 557, '' from dual union all
select 374, '' from dual union all
select 513, '' from dual union all
select 174, '' from dual union all
select 406, '' from dual union all
select 469, '' from dual union all
select 179, '' from dual union all
select 240, '' from dual union all
select 209, '' from dual union all
select 550, '' from dual union all
select 249, '' from dual union all
select 254, '' from dual union all
select 510, '' from dual union all
select 553, '' from dual union all
select 546, '' from dual union all
select 504, '' from dual union all
select 403, '' from dual union all
select 311, '' from dual union all
select 437, '' from dual union all
select 496, '' from dual union all
select 520, '' from dual union all
select 408, '' from dual union all
select 471, '' from dual union all
select 307, '' from dual union all
select 294, '' from dual union all
select 481, '' from dual union all
select 601, '' from dual union all
select 394, '' from dual union all
select 398, '' from dual union all
select 419, '' from dual union all
select 540, '' from dual union all
select 421, '' from dual union all
select 569, '15й этаж' from dual union all
select 590, '15й этаж' from dual union all
select 378, '' from dual union all
select 598, 'ТестНСИ' from dual union all
select 599, '' from dual union all
select 596, '' from dual union all
select 564, '' from dual union all
select 562, '' from dual union all
select 424, '' from dual union all
select 109, '' from dual union all
select 1, '' from dual union all
select 54, '' from dual union all
select 490, '' from dual union all
select 139, '' from dual union all
select 187, '' from dual union all
select 392, '''FROM USER''' from dual union all
select 392, '6' from dual union all
select 484, 'АПБ' from dual union all
select 368, 'АПБ' from dual union all
select 368, '''FROM USER''' from dual union all
select 368, 'Тестовая группа' from dual union all
select 368, '6' from dual union all
select 368, 'ЙЦу' from dual union all
select 368, 'Тест' from dual union all
select 368, 'Тестирование' from dual union all
select 368, 'Тестирование' from dual union all
select 367, 'test' from dual union all
select 368, 'test' from dual union all
select 377, 'test' from dual union all
select 368, 'Группа для теста' from dual union all
select 376, '' from dual union all
select 608, 'ТестНСИ' from dual union all
select 241, '' from dual union all
select 20, '' from dual union all
select 116, '' from dual union all
select 26, '' from dual union all
select 358, '' from dual union all
select 404, '' from dual union all
select 405, '' from dual union all
select 410, '' from dual union all
select 412, '' from dual union all
select 566, '' from dual union all
select 485, '' from dual union all
select 483, '' from dual union all
select 521, '' from dual union all
select 441, '' from dual union all
select 442, '' from dual union all
select 379, '' from dual union all
select 383, '' from dual),
source_rows as
(select version_id, device_group_name,
row_number() over (partition by version_id order by device_group_name) rn2,
dense_rank() over (order by version_id, device_group_name) rn
from source_rows0),
source_rows2 as
(select * from source_rows
where device_group_name is not null),
source_rows3 as
(select version_id, device_group_name, rn2,
row_number() over (order by version_id)+(select max(rn) from source_rows2) rn
from source_rows
where device_group_name is null)
select rn, rn2, version_id, device_group_name from source_rows2
union all
select rn, rn2, version_id, device_group_name from source_rows3
order by rn, rn2, version_id
Но получилось не то, что нужно:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
select 367 version_id, 'test' device_group_name from dual union all
select 368, '''FROM USER''' from dual union all
select 368, 'test' from dual union all
select 368, 'АПБ' from dual union all
select 368, 'Группа для теста' from dual union all
select 368, 'ЙЦу' from dual union all
select 368, 'Тест' from dual union all
select 368, 'Тестирование' from dual union all
select 368, 'Тестирование' from dual union all
select 368, 'Тестовая группа' from dual union all
select 368, '6' from dual union all
select 377, 'test' from dual union all
select 392, '''FROM USER''' from dual union all
select 392, '6' from dual union all
select 484, 'АПБ' from dual union all
select 569, '15й этаж' from dual union all
select 590, '15й этаж' from dual union all
select 598, 'ТестНСИ' from dual union all
select 608, 'ТестНСИ' from dual union all
select 1, '' from dual union all
select 6, '' from dual union all
select 8, '' from dual union all
select 14, '' from dual union all
select 15, '' from dual union all
select 16, '' from dual union all
select 20, '' from dual union all
select 23, '' from dual union all
select 26, '' from dual union all
select 29, '' from dual union all
select 33, '' from dual union all
select 35, '' from dual union all
select 36, '' from dual union all
select 42, '' from dual union all
select 44, '' from dual union all
select 45, '' from dual union all
select 54, '' from dual union all
select 58, '' from dual union all
select 66, '' from dual union all
select 75, '' from dual union all
select 79, '' from dual union all
select 109, '' from dual union all
select 111, '' from dual union all
select 112, '' from dual union all
select 116, '' from dual union all
select 124, '' from dual union all
select 125, '' from dual union all
select 139, '' from dual union all
select 140, '' from dual union all
select 156, '' from dual union all
select 169, '' from dual union all
select 171, '' from dual union all
select 172, '' from dual union all
select 174, '' from dual union all
select 179, '' from dual union all
select 182, '' from dual union all
select 187, '' from dual union all
select 209, '' from dual union all
select 235, '' from dual union all
select 237, '' from dual union all
select 240, '' from dual union all
select 241, '' from dual union all
select 249, '' from dual union all
select 253, '' from dual union all
select 254, '' from dual union all
select 294, '' from dual union all
select 307, '' from dual union all
select 311, '' from dual union all
select 317, '' from dual union all
select 318, '' from dual union all
select 319, '' from dual union all
select 358, '' from dual union all
select 373, '' from dual union all
select 374, '' from dual union all
select 376, '' from dual union all
select 378, '' from dual union all
select 379, '' from dual union all
select 383, '' from dual union all
select 390, '' from dual union all
select 394, '' from dual union all
select 398, '' from dual union all
select 403, '' from dual union all
select 404, '' from dual union all
select 405, '' from dual union all
select 406, '' from dual union all
select 408, '' from dual union all
select 410, '' from dual union all
select 412, '' from dual union all
select 419, '' from dual union all
select 421, '' from dual union all
select 424, '' from dual union all
select 437, '' from dual union all
select 441, '' from dual union all
select 442, '' from dual union all
select 464, '' from dual union all
select 469, '' from dual union all
select 471, '' from dual union all
select 481, '' from dual union all
select 483, '' from dual union all
select 485, '' from dual union all
select 490, '' from dual union all
select 496, '' from dual union all
select 504, '' from dual union all
select 510, '' from dual union all
select 513, '' from dual union all
select 520, '' from dual union all
select 521, '' from dual union all
select 532, '' from dual union all
select 540, '' from dual union all
select 546, '' from dual union all
select 550, '' from dual union all
select 552, '' from dual union all
select 553, '' from dual union all
select 555, '' from dual union all
select 557, '' from dual union all
select 562, '' from dual union all
select 564, '' from dual union all
select 566, '' from dual union all
select 596, '' from dual union all
select 599, '' from dual union all
select 601, '' from dual
Как можно добиться желаемого результата? |
