选择不在group by语句上的列

Question

那么，我有一个名为RACES的表，其中有以下列

raceid | year | round

我想获得每年最后一场比赛的raceid，它将在每年的圆形列中具有最高的值。所以我做了下面的查询:

SELECT
year,
max(round)
FROM races
group by year
order by year;

结果是:

year    max(round)
1950    7
1951    8
1952    8
1953    9
1954    9

现在如果我做

SELECT * FROM RACES WHERE YEAR = 1950;

我得到这个:

raceid  year    round
833     1950    1
834     1950    2
835     1950    3
836     1950    4
837     1950    5
838     1950    6
839     1950    7

由于子弹数随时间变化，1951年可能不是7发而是10发。所以我总是想要获得特定年份的最大回合值，然后将其与相应的raceid一起打印出来。本例中为839

由于raceid不在by组中，所以我不能在第一次查询中选择它。我该怎么做呢?

Answer 1

select year, max(round) as max_round, 
max(raceid) keep (dense_rank last order by round) as raceid
from   races
group  by year
order  by year
;

first和last函数文档:

https://docs.oracle.com/database/121/SQLRF/functions074.htm SQLRF00641

Answer 2

使用您发布的示例数据，这只是另一个MAX函数调用:

SQL> with races (raceid, year, round) as
2    (select 700, 1940, 1 from dual union all
3     select 701, 1940, 2 from dual union all
4     --
5     select 833, 1950, 1 from dual union all
6     select 838, 1950, 6 from dual union all
7     select 839, 1950, 7 from dual
8    )
9  select year,
10        max(round) max_round,
11        max(raceid) max_raceid
12  from races
13  group by year;
YEAR  MAX_ROUND MAX_RACEID
---------- ---------- ----------
1940          2        701
1950          7        839
SQL>

---

但是，如果RACEID不是MAX和ROUND，那么您可以将当前查询用作子查询(或CTE)，并将其与"原始"连接起来。表:

SQL> with races (raceid, year, round) as
2    (select 700, 1940, 1 from dual union all
3     select 701, 1940, 2 from dual union all
4     --
5     select 900, 1950, 1 from dual union all  --> 900 is MAX for year 1950
6     select 838, 1950, 6 from dual union all
7     select 839, 1950, 7 from dual
8    ),
9  mrid as
10    (select year,
11            max(round) max_round
12     from races
13     group by year
14    )
15  select m.year,
16         m.max_round,
17         r.raceid
18  from mrid m join races r on r.year = m.year and r.round = m.max_round;
YEAR  MAX_ROUND     RACEID
---------- ---------- ----------
1940          2        701
1950          7        839
SQL>