Oracle SQL：使用多个条件只获得一个最大行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8791322/
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
Oracle SQL: getting only one max row using multiple criteria
提问by jllopezpino
I have this table:
我有这张表：
Department NAME EMAIL ID DATE1 DATE2
1 John [email protected] 74 05/04/2007 05/04/2007
1 Sam [email protected] 23 05/04/2007 05/04/2007
1 Tom [email protected] 46 05/04/2007 03/04/2007
1 Bob [email protected] 23 01/01/2006
2 Tom [email protected] 62 02/02/2000 05/05/1997
I want to get the row (only one per department) with max DATE1, but it's not unique! So if there is multiple results I want to get the max DATE2, and if there are multiple ones then the one with the biggest ID is returned.
我想用 max 获得该行（每个部门只有一个）DATE1，但它不是唯一的！因此，如果有多个结果，我想获得 max DATE2，如果有多个结果，则返回 ID 最大的那个。
So there result of the query would be:
所以查询的结果是：
1 John [email protected] 74 05/04/2007 05/04/2007
2 Tom [email protected] 62 02/02/2000 05/05/1997
Thank you very much.
非常感谢。
回答by Bassam Mehanni
You need to use the ROW_NUMBER function:
您需要使用 ROW_NUMBER 函数：
SELECT Department, NAME, EMAIL, ID, DATE1, DATE2
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Department ORDER BY DATE1 DESC, DATE2 DESC, ID DESC) AS RowNumber,
Department, NAME, EMAIL, ID, DATE1, DATE2
FROM MyTable ) t
WHERE RowNumber = 1
回答by Eric
Use the overclause:
使用over子句：
select
*
from
(
select
Department,
Name,
Email,
ID,
DATE1,
DATE2,
max(DATE1) over (partition by Department) as MaxDate1,
max(DATE2) over (partition by Department, DATE1) as MaxDate2,
max(ID) over (partition by Department, DATE1, DATE2) as MaxID
from
employees
) x
where
x.DATE1 = x.MaxDate1
and x.DATE2 = x.MaxDate2
and x.ID = x.MaxID
回答by symcbean
Something like....
就像是....
SELECT y2.*
FROM
(SELECT dept,
MAX(
TO_CHAR(date1,'YYYYHH24MISS') || TO_CHAR(date2,'YYYYHH24MISS')
|| id) as lastrec
FROM yourtable y1
GROUP BY dept) as ilv,
yourtable y2
WHERE y2.id=TO_NUMBER(SUBSTR(y2.lastrec, 21))
回答by jj2422
SELECT
*
FROM
(
SELECT
Department,
Name,
Email,
ID,
DATE1,
DATE2,
max(DATE1) over (partition by Department) as MaxDate1,
max(DATE2) over (partition by Department, DATE1) as MaxDate2,
max(ID) over (partition by Department, DATE1, DATE2) as MaxID
FROM
employees
)
WHERE
x.DATE1 = x.MaxDate1
AND x.DATE2 = x.MaxDate2
AND x.ID = x.MaxID
