英文:
Max function is not filtering results
问题
我试图根据最后更新日期字段获取最近的开始日期。当我尝试使用max函数时,它没有筛选结果,仅显示最近的数据。
select max(a.LAST_UPDATED_DATE),a.term_start
from table AS a
where a.ID='123456' and location='Colorado'
group by 2
这是我得到的输出:
MAX(a.LAST_UPDATED_DATE) | TERM_START |
---|---|
2023-02-27T18:27:50Z | 2023-04-12T07:00:00Z |
2023-01-24T18:26:44Z | 2023-04-05T07:00:00Z |
我只想得到:
MAX(a.LAST_UPDATED_DATE) | TERM_START |
---|---|
2023-02-27T18:27:50Z | 2023-04-12T07:00:00Z |
英文:
I am trying to only get the most recent start date based on the last updated date field. When I try to use max, it is not filtering the results to only the most recent data.
select max(a.LAST_UPDATED_DATE),a.term_start
from table AS a
where a.ID='123456' and location='Colorado'
group by 2
This is the output I am getting
MAX(a.LAST_UPDATED_DATE) | TERM_START |
---|---|
2023-02-27T18:27:50Z | 2023-04-12T07:00:00Z |
2023-01-24T18:26:44Z | 2023-04-05T07:00:00Z |
I only want to get
MAX(a.LAST_UPDATED_DATE) | TERM_START |
---|---|
2023-02-27T18:27:50Z | 2023-04-12T07:00:00Z |
答案1
得分: 1
如果您可以访问窗口函数,您可以使用它
选择 max(a.LAST_UPDATED_DATE) 分区按 (a.ID, location) ,a.term_start
从表格 作为 a
其中 a.ID='123456' 并且 location='科罗拉多'
英文:
If you have access to window function, you can use it
select max(a.LAST_UPDATED_DATE) partition by (a.ID, location) ,a.term_start
from table AS a
where a.ID='123456' and location='Colorado'
答案2
得分: 0
我已经在Oracle、MS Server和HANA上尝试过这个操作:
Select
*
from
(
select
ROW_NUMBER() OVER( PARTITION BY a.ID, a.location
ORDER BY term_start DESC, a.LAST_UPDATED_DATE DESC
) row_num,
a.LAST_UPDATED_DATE,
a.term_start
from a
where a.ID='123456' and
location='Colorado'
) as test
where row_num = 1
如果你移除 where a.ID='123456' and location='Colorado'
,它仍然可以工作,并且你会在结果中看到所有的位置和ID。
在分区中使用每个 a.ID 和 location 的组合,其中 row_num = 1 返回了最近的值,因为在分区内,这些值是按 term_start DESC、a.LAST_UPDATED_DATE DESC 排序的。
在你的代码中,你正在以 term_start 进行分组,这会导致 TERM_START 的唯一值,因此有两行。也许你想要以 a.ID 和 location 进行分组。这样你就会得到每个位置的 MAX(a.LAST_UPDATED)。在查询的意图中,你还提到你想要最近的值。MAX 和最近不一定是相同的。
英文:
I've tried this on Oracle, MS Server, and HANA:
Select
*
from
(
select
ROW_NUMBER() OVER( PARTITION BY a.ID, a.location
ORDER BY term_start DESC, a.LAST_UPDATED_DATE DESC
) row_num,
a.LAST_UPDATED_DATE,
a.term_start
from a
where a.ID='123456' and
location='Colorado'
) as test
where row_num = 1
It will still work if you remove `where a.ID='123456' and location='Colorado' and you will see all of the locations and ids in your result
Using each a.ID and location combination in the partition where row_num = 1 returns the most recent value since within the partition the values are ordered term_start DESC, a.LAST_UPDATED_DATED_DATE DESC.
In your code, you are grouping on the term_start which results in unique values for TERM_START, therefore, two rows. You might want to group on the a.ID and location instead. You will get the MAX (a.LAST_UPDATED) per location. In the intent of the query you also mentioned you wanted most recent. The MAX and Most recent are not necessarily the same.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论