SQL-如何选择具有最大值的列的行(+分组依据) [英] SQL - How to select a row having a column with max value (+ group by)

问题描述

我正在建立此处引用的问题: SQL-如何选择具有以下内容的行具有最大值的列

I'm building up on the question referenced here: SQL - How to select a row having a column with max value

date                 value

18/5/2010, 1 pm        40
18/5/2010, 2 pm        20
18/5/2010, 3 pm        60
18/5/2010, 4 pm        30
18/5/2010, 5 pm        60
18/5/2010, 6 pm        25 

我需要查询具有max(value)(即60)的行.所以，在这里，我们 得到两行.因此，我需要具有最低时间戳的行 那天(即2010年5月18日，下午3点-> 60)

i need to query for the row having max(value)(i.e. 60). So, here we get two rows. From that, I need the row with the lowest time stamp for that day(i.e 18/5/2010, 3 pm -> 60)

我们如何以Sujee提供的答案为基础

How can we build on the answer as provided by Sujee:

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc)
where rownum <= 1

如果数据具有第三列类别".

if the data has a 3rd column "category".

date                 value    category

18/5/2010, 1 pm        40      1
18/5/2010, 2 pm        20      1
18/5/2010, 3 pm        60      1
18/5/2010, 4 pm        30      2
18/5/2010, 5 pm        60      2
18/5/2010, 6 pm        25      2 

仅供参考-我正在使用Oracle，并试图避免嵌套连接(因此使用rownum技巧)

FYI - I'm using Oracle, and trying to avoid a nested join (hence the rownum trick)

目标是得到相同的答案，但按类别分组

The goal is to have the same answer, but with a group by category

推荐答案

听起来您想为每个类别选择具有最高high_val的行.如果是这样，您可以使用row_number()根据其high_val值对类别中的每一行进行排名，并且仅选择排名最高的行，即rn = 1:

It sounds like you want to select the row with the highest high_val for each category. If so, you can use row_number() to rank each row within a category according to its high_val value, and only select the highest ranked rows i.e. rn = 1:

select * from (
    select row_number() over (partition by category order by high_val desc, date asc) rn, *
    from mytable
    where something = 'avalue'
) t1 where rn = 1

这篇关于SQL-如何选择具有最大值的列的行(+分组依据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！