-
Notifications
You must be signed in to change notification settings - Fork 24
/
group_by_vs_over.sql
38 lines (28 loc) · 1.04 KB
/
group_by_vs_over.sql
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
create table tq84_group_by_vs_over (
a varchar2(3),
b number
);
insert into tq84_group_by_vs_over values ('foo', 42);
insert into tq84_group_by_vs_over values ('foo', 10);
insert into tq84_group_by_vs_over values ('foo', 13);
insert into tq84_group_by_vs_over values ('bar', 7);
insert into tq84_group_by_vs_over values ('bar', 39);
insert into tq84_group_by_vs_over values ('bar', 88);
insert into tq84_group_by_vs_over values ('baz', 21);
----------------------------------------------------
select
max(b) keep (dense_rank first order by b desc) over (partition by a) max_b_for_a,
-- ^^^^^^^^^^^^^^^^^^^^^
-- Missing in 2nd query
a
from tq84_group_by_vs_over
;
----------------------------------------------------
select
max(b) keep (dense_rank first order by b desc) max_b_for_a,
a
from tq84_group_by_vs_over
group by a -- <<< Missing in 1st query
;
----------------------------------------------------
drop table tq84_group_by_vs_over purge;