sql - sql根据属性保留第一行

Question

我写了一个 sql 来按 ASC 中的 SEASON 和 DESC 中的 CHAM 分组。如何选择每个 SEASON 中最大的 CHAM 值？为了进一步发展，我需要保留 CHAMPION_ID。

标签： sqlmariadb

Answer 1

Generally, you can do this with window functions, but your database, MariaDB v5.5, is too old to support window functions and a bunch of other things. I'd recommend either upgrading MariaDB or doing your work on dbfiddle or using the stand-alone SQLite.

You can do this without window functions using a subquery.

select *
from champs as c1
where cham = (
  select max(cham)
  from champs as c2
  where c1.season = c2.season
)

Pick only the rows whose cham equals the highest cham for that season.

You might be tempted to use a group by, but this will not show duplicates. For example, spring is all ties.

-- Only shows one row per season.
select *
from champs
group by season
having cham = max(cham);

Try it.

---

With window functions...

Add the seasonal ranks. This is partitioned by season and ordering by cham. rank() will assign the same ranks to ties.

select
  *,
  rank() over (partition by season order by cham desc) as season_rank
from champs

Then use that as a common table expression and select only the rows with seasonal_rank = 1.

with ranked as (
  select
    *,
    rank() over (partition by season order by cham desc) as season_rank
  from champs
)
select *
from ranked
where season_rank = 1;

Try it.