英文:
Return line of data with most recent date
问题
我正在使用Oracle SQL，尝试根据most_recent_date返回最新的category。我已成功提取每个part no/niin下每个category的most_recent_date，但无法获得我需要的完整结果。
我有以下查询：
SELECT DISTINCT
contract,
part_no,
niin,
category,
MAX(date_entered) OVER(PARTITION BY contract, part_no, niin, category) most_recent_date
FROM
( **内部查询** )
当前结果：
CONTRACT PN NIIN 1T 01-AUG-08
CONTRACT PN NIIN 7T 19-APR-22
我的最终结果只需要具有最新日期的那一行。下面是所需的行：
CONTRACT PN NIIN 7T 19-APR-22
我尝试嵌套再次选择语句，然后尝试选择最新的行，但仍然收到相同的两行。我还尝试在category的分组语句中使用most_recent_date，但也没有成功。
我确信这是一个简单的修复，但我找不到它。
英文:
I'm using Oracle SQL and trying to return the most recent category dependent on the most_recent_date. I am able to successfully pull the most_recent_date per category per part no/niin, but I'm not able to get the full results I need.
I have the below query:
SELECT DISTINCT
contract,
part_no,
niin,
category,
MAX(date_entered) OVER(PARTITION BY contract, part_no, niin, category) most_recent_date
FROM
( **inner query** )
Current result:
CONTRACT PN NIIN 1T 01-AUG-08
CONTRACT PN NIIN 7T 19-APR-22
My End result for this needs to be just the line that has the most recent date. Line needed below:
CONTRACT PN NIIN 7T 19-APR-22
I have tried to nest the select statement again, to then try and choose the most recent line, but still received the same 2 lines. I also tried to use most_recent_date within a grouping statement for category and that didn't work either.
I'm sure it's a simple fix, but I'm not finding it.
答案1
得分: 1
ROW_NUMBER函数根据它们输入的日期顺序为每一行分配一个编号，每个类别从1开始。因此，每个类别中最近日期的行将具有编号1。
在外部查询中，我们仅选择分配的编号为1的行。这意味着我们选择了每个类别中最近日期的行。
SELECT CONTRACT, PN, NIIN, CATEGORY, DATE_ENTERED
FROM (
SELECT
CONTRACT,
PN,
NIIN,
CATEGORY,
DATE_ENTERED,
ROW_NUMBER() OVER (PARTITION BY CONTRACT, PN, NIIN, CATEGORY ORDER BY DATE_ENTERED DESC) AS rn
FROM
( 内部查询 )
) 子查询
WHERE rn = 1;
英文:
The ROW_NUMBER function assigns a number to each row based on the order of the dates they were entered, starting from 1 for each category. So, the row with the most recent date within each category will have the number 1.
In the outer query, we select only the rows where the assigned number is 1. This means we are choosing the row with the most recent date for each category.
SELECT CONTRACT, PN, NIIN, CATEGORY, DATE_ENTERED
FROM (
SELECT
CONTRACT,
PN,
NIIN,
CATEGORY,
DATE_ENTERED,
ROW_NUMBER() OVER (PARTITION BY CONTRACT, PN, NIIN, CATEGORY ORDER BY DATE_ENTERED DESC) AS rn
FROM
( **inner query** )
) subquery
WHERE rn = 1;
答案2
得分: 1
在Oracle中，您可以使用group by和keep语法来获取与最新日期对应的类别：
select contract, part_no, niin,
max(date_entered) last_date,
max(category) keep(dense_rank last order by date_entered) last_category
from ....
group by contract, part_no, niin
英文:
In Oracle, you could use group by and the keep syntax to bring the category that corresponds to the latest date:
select contract, part_no, niin,
max(date_entered) last_date,
max(category) keep(dense_rank last order by date_entered) last_category
from ....
group by contract, part_no, niin
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论