如果行具有不同的值，则选择max(date)不起作用，我只想获取日期最高的行 [英] Select max(date) does not work if the rows have different values, I only want to fetch the row with the highest date

问题描述

这是代码:

select security,max(dte),close,ask,bid,mid from k$prices
where to_char(dte,'MON-YYYY') = 'JAN-2021'
group by security,close,ask,bid,mid,dte
order by security,dte desc

下面是结果:我只想获得2行，每个行的日期都最高(436年1月5日和448年1月29日)，但是因为字段的值不同，所以仍显示所有行.请帮我.谢谢

Below is the result: I only want to get 2 rows, which has the highest date for each security (436 January 5 and 448 January 29) but because the values of the fields are different, all rows are still being shown. Please help me. Thanks

推荐答案

您可以在内联视图t中首先对所有行进行排名，然后仅选择那些排名为1(rnb = 1)的行

You could rank all your rows first within the inline view t, then select only those that have rank 1 ( rnb = 1)

select security, dte, close, ask, bid, mid
from (
  select security, dte, close, ask, bid, mid, row_number()over(partition by security order by dte desc) rnb
  from your_table
)t
where rnb = 1
;

这篇关于如果行具有不同的值，则选择max(date)不起作用，我只想获取日期最高的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！