Here is my test set:
这是我的测试集:
master_ref ref value
56279276 56279325 FRAME ASSEMBLY1
56279276 384062724 FRAME ASSEMBLY2
56279276 443450775 FRAME ASSEMBLY3
I want to retrieve the value field based on the highest ref given a master_ref.
我想根据给定master_ref的最高引用来检索值字段。
Here is what I tried that just return everything:
这是我尝试过的只返回一切:
select first_value(value) over (partition by ida3masterreference order by ida3a4 desc) value, ida3masterreference, ida3a4 from sts_epm_title1;
I expected to only get one result:
我希望只得到一个结果:
master_ref ref value
56279276 443450775 FRAME ASSEMBLY3
But it still returns all 3 results. What am I doing wrong? Thanks!
但它仍然返回所有3个结果。我究竟做错了什么?谢谢!
2 个解决方案
#1
3
first_value()
is an analytic function, so it does not reduce the number of rows. You apparently want an aggregation function so use the keep
syntax:
first_value()是一个分析函数,因此它不会减少行数。你显然想要一个聚合函数,所以使用keep语法:
select max(value) keep (dense_rank first order by ida3a4 desc) as value,
ida3masterreference, max(ida3a4) as ida3a4
from sts_epm_title1
group by ida3masterreference
#2
0
You could use ROW_NUMBER/RANK
:
你可以使用ROW_NUMBER / RANK:
WITH cte AS (
select row_number() over (partition by ida3masterreference
order by ida3a4 desc) AS rn, t.*
from sts_epm_title1 t
)
SELECT *
FROM cte
WHERE rn = 1;
#1
3
first_value()
is an analytic function, so it does not reduce the number of rows. You apparently want an aggregation function so use the keep
syntax:
first_value()是一个分析函数,因此它不会减少行数。你显然想要一个聚合函数,所以使用keep语法:
select max(value) keep (dense_rank first order by ida3a4 desc) as value,
ida3masterreference, max(ida3a4) as ida3a4
from sts_epm_title1
group by ida3masterreference
#2
0
You could use ROW_NUMBER/RANK
:
你可以使用ROW_NUMBER / RANK:
WITH cte AS (
select row_number() over (partition by ida3masterreference
order by ida3a4 desc) AS rn, t.*
from sts_epm_title1 t
)
SELECT *
FROM cte
WHERE rn = 1;