Oracle SQL：使用多个条件获取仅一个最大行

Question

我有这个表格：

Department   NAME   EMAIL         ID     DATE1         DATE2
1            John   [e-mail]   74     05/04/2007    05/04/2007
1            Sam    [e-mail]   23     05/04/2007    05/04/2007
1            Tom    [e-mail]   46     05/04/2007    03/04/2007
1            Bob    [e-mail]   23     01/01/2006
2            Tom    [e-mail]   62     02/02/2000    05/05/1997

我希望能够获取每个部门最大DATE1的一行（每个部门只有一行），但这并不唯一！如果有多个结果，我想获取最大的DATE2，如果有多个，则返回ID最大的那一个。

所以查询的结果将是：

1            John   [e-mail]   74     05/04/2007    05/04/2007
2            Tom    [e-mail]   62     02/02/2000    05/05/1997

非常感谢您。

- jllopezpino

Answer 1

您需要使用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

- Bassam Mehanni

---

听起来很不错！它适用于多列分区吗？因为我的表格更加复杂。 - jllopezpino

@user337746 - 是的，partition by 的工作方式与 group by 语句完全相同。 - Eric

是的，partition by与group by子句类似，但用于Row_Number函数。 - Bassam Mehanni

@user337746 很高兴能帮到您，请不要忘记标记正确答案以供日后参考。 - Bassam Mehanni

@BassamMehanni 我在Oracle上尝试了一下，Department后面不应该有逗号，只需要在Order BY之前加一个空格就可以了。感谢您提供的解决方案。 - jero

@jero 已修复！感谢您的提醒。 - Bassam Mehanni

Answer 2

使用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

- Eric

---

不错，还挺新颖的。但是只使用row_number不会更轻松吗？ - MatBailie

@Dems - 当然可以。如果这只是一个示例，他需要更细粒度的控制，我想提供另一种方法来实现它。 - Eric

Answer 3

类似于......

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))

- symcbean

---

哦，我明白了。但它假设ID字段是唯一的，这一点还没有说明。不过这确实是很好的侧面思考。 - MatBailie

Answer 4

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

- jj2422