oracle 记录最大日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8898020/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Taking the record with the max date
提问by Revious
Let's assume I extract some set of data.
假设我提取了一些数据集。
i.e.
IE
SELECT A, date
FROM table
I want just the record with the max date (for each value of A). I could write
我只想要最大日期的记录（对于 A 的每个值）。我可以写
SELECT A, col_date
FROM TABLENAME t_ext
WHERE col_date = (SELECT MAX (col_date)
FROM TABLENAME t_in
WHERE t_in.A = t_ext.A)
But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?
但是我的查询真的很长……有没有更紧凑的方法使用 ANALYTIC FUNCTION 来做同样的事情？
回答by Justin Cave
The analytic function approach would look something like
分析函数方法看起来像
SELECT a, some_date_column
FROM (SELECT a,
some_date_column,
rank() over (partition by a order by some_date_column desc) rnk
FROM tablename)
WHERE rnk = 1
Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBERor the DENSE_RANKanalytic function rather than RANK.
请注意，根据您希望如何处理关联（或您的数据模型中是否可能存在关联），您可能希望使用ROW_NUMBER或DENSE_RANK分析函数而不是RANK。
回答by Benoit
If dateand col_dateare the same columns you should simply do:
如果date和col_date是相同的列，您应该简单地执行以下操作：
SELECT A, MAX(date) FROM t GROUP BY A
Why not use:
为什么不使用：
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME )
SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
Otherwise:
除此以外：
SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
FROM TABLENAME
GROUP BY A
回答by ypercube??
You could also use:
您还可以使用：
SELECT t.*
FROM
TABLENAME t
JOIN
( SELECT A, MAX(col_date) AS col_date
FROM TABLENAME
GROUP BY A
) m
ON m.A = t.A
AND m.col_date = t.col_date
回答by user2778168
A is the key, max(date) is the value, we might simplify the query as below:
A 是键，max(date) 是值，我们可以将查询简化如下：
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
回答by Aitor
Justin Cave answer is the best, but if you want antoher option, try this:
贾斯汀洞穴的答案是最好的，但如果你想要另一个选项，试试这个：
select A,col_date
from (select A,col_date
from tablename
order by col_date desc)
where rownum<2
回答by Robert
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
