I am trying to get the maximum value in a column based on another value in a row.
我试图根据一行中的另一个值获取列中的最大值。
I have the following code :
我有以下代码：
UPDATE LeweringVsSkattingResultaat
SET maks = ((SELECT max(persentklaarkultivar2) FROM
LeweringVsSkattingResultaat)
group by kultivar2)
I get the following error :Incorrect syntax near the keyword 'group'.
我收到以下错误：关键字“group”附近的语法不正确。
I want the maksimum value in column persentklaarkultivar2 for each value in kultivar2.
对于kultivar2中的每个值，我想要列persentklaarkultivar2中的maksimum值。
Any help would be much appreciated.
任何帮助将非常感激。
Regards
问候
2 个解决方案
#1
1
Your subquery would generate an error if you have more than on value for kultivar2. The group by would return a row for each kultivar2.
如果kultivar2的值大于on，则子查询将生成错误。 group by将为每个kultivar2返回一行。
Although you can use a correlated subquery to fix the problem (see end of answer), I like to do this with updatable CTEs and window functions:
虽然您可以使用相关子查询来解决问题（请参阅答案结束），但我喜欢使用可更新的CTE和窗口函数来执行此操作：
with toupdate as (
select r.*,
max(persentklaarkultivar2) over (partition by kultivar2) as maxval
from LeweringVsSkattingResultaat r
)
update toupdate
set maks = maxval;
I should note that with window functions, you can readily calculate the maximum whenever you want, so it is not necessary to store it. Window functions are optimized so they can take advantage of an index on LeweringVsSkattingResultaat(kultivar2, persentklaarkultivar2).
我应该注意，使用窗口函数，您可以随时计算最大值，因此无需存储它。窗口函数经过优化，因此可以利用LeweringVsSkattingResultaat（kultivar2，persentklaarkultivar2）上的索引。
This is probably a better approach. You won't have to figure out how to keep the maks value up-to-date when rows are inserted, updated, or deleted from the table.
这可能是一种更好的方法。在从表中插入，更新或删除行时，您不必弄清楚如何使mak值保持最新。
The correlated subquery would look like:
相关子查询看起来像：
UPDATE r
SET maks = (SELECT max(r2.persentklaarkultivar2)
FROM LeweringVsSkattingResultaat r2
WHERE r2.kultivar2 = r.kultivar2
)
FROM LeweringVsSkattingResultaat r;
#2
0
Remove a ()
删除（）
UPDATE LeweringVsSkattingResultaat
SET maks = ( SELECT max(persentklaarkultivar2) FROM
LeweringVsSkattingResultaat
group by kultivar2)
otherwise your group by is out the inner select
否则你的小组是在内部选择
#1
1
Your subquery would generate an error if you have more than on value for kultivar2. The group by would return a row for each kultivar2.
如果kultivar2的值大于on，则子查询将生成错误。 group by将为每个kultivar2返回一行。
Although you can use a correlated subquery to fix the problem (see end of answer), I like to do this with updatable CTEs and window functions:
虽然您可以使用相关子查询来解决问题（请参阅答案结束），但我喜欢使用可更新的CTE和窗口函数来执行此操作：
with toupdate as (
select r.*,
max(persentklaarkultivar2) over (partition by kultivar2) as maxval
from LeweringVsSkattingResultaat r
)
update toupdate
set maks = maxval;
I should note that with window functions, you can readily calculate the maximum whenever you want, so it is not necessary to store it. Window functions are optimized so they can take advantage of an index on LeweringVsSkattingResultaat(kultivar2, persentklaarkultivar2).
我应该注意，使用窗口函数，您可以随时计算最大值，因此无需存储它。窗口函数经过优化，因此可以利用LeweringVsSkattingResultaat（kultivar2，persentklaarkultivar2）上的索引。
This is probably a better approach. You won't have to figure out how to keep the maks value up-to-date when rows are inserted, updated, or deleted from the table.
这可能是一种更好的方法。在从表中插入，更新或删除行时，您不必弄清楚如何使mak值保持最新。
The correlated subquery would look like:
相关子查询看起来像：
UPDATE r
SET maks = (SELECT max(r2.persentklaarkultivar2)
FROM LeweringVsSkattingResultaat r2
WHERE r2.kultivar2 = r.kultivar2
)
FROM LeweringVsSkattingResultaat r;
#2
0
Remove a ()
删除（）
UPDATE LeweringVsSkattingResultaat
SET maks = ( SELECT max(persentklaarkultivar2) FROM
LeweringVsSkattingResultaat
group by kultivar2)
otherwise your group by is out the inner select
否则你的小组是在内部选择