根据max(cases)和min(date)选择所有行

ef1yzkbh 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(248)

我有一张table，我的table看起来像这样：

location     | date     | new_cases | total_deaths | new_deaths
----------------------------------------------------------------
Afghanistan   2020-04-07    38             7             0
Afghanistan   2020-04-08    30             11            4
Afghanistan   2020-04-09    56             14            3
Afghanistan   2020-04-10    61             15            1
Afghanistan   2020-04-11    37             15            0
Afghanistan   2020-04-12    34             18            3

在这种情况下，我想基于max（new\u cases）获取行位置，这是我的查询：

select a.*
from covid a
    join (
    select location, max(new_cases) highest_case
    from covid 
    group by location 
    ) b 
on a.location = b.location
and a.new_cases = b.highest_case

但我发现相同的位置和最大值（case）与不同的日期值，这是结果。

location     | date     | new_cases | total_deaths | new_deaths
----------------------------------------------------------------
Bhutan        2020-06-08    11           0              0
Bolivia       2020-07-28    2382         2647           64
Bonaire Sint  2020-04-02    2            0              0
Bonaire Sint  2020-07-15    2            0              0
Botswana      2020-07-24    164          1              0

现在，我如何得到基于min（date）的值，请给我一些建议来解决这个问题，输出应该是这样的：

location     | date     | new_cases | total_deaths | new_deaths
----------------------------------------------------------------
Bhutan        2020-06-08    11           0              0
Bolivia       2020-07-28    2382         2647           64
Bonaire Sint  2020-04-02    2            0              0
Botswana      2020-07-24    164          1              0

sql postgresql

来源：https://stackoverflow.com/questions/63283908/select-all-rows-based-on-maxcases-and-mindate

2条答案

按热度按时间

anauzrmj1#

您可以使用窗口函数max（）获取最大\u案例（根据位置），然后对行进行编号（获取最小日期）：

select location,date,new_cases,total_deaths,new_deaths from
(
--get min date with max_cases
select row_number()over(partition by location order by date)n,date,
location,new_cases,total_deaths,new_deaths
 from
   (
     select location,date,max(new_cases)over(partition by 
     location)max_case,new_cases,total_deaths,new_deaths from covid  --get max_case
   ) X
  where new_cases=max_case   --fetch only max case
)Y where n=1

赞(0）回复(0）举报 2021-07-24

tjvv9vkg2#

使用 distinct on :

select distinct on (location) c.*
from covid c
order by location, new_cases desc;

对于最小日期，请使用：

order by location, date asc;

赞(0）回复(0）举报 2021-07-24

我来回答

根据max(cases)和min(date)选择所有行

2条答案

相关问题

热门标签

最新问答