sql-server - SQL Server : Second maximum value per group

Question

我有一个表格，数据如下

CREATE TABLE Tbl
(
  ID VARCHAR(20)
  , Desc1 VARCHAR(30)
  , BALANCE_DATE DATE
);

INSERT INTO Tbl (ID, Desc1, BALANCE_DATE)
VALUES ('ID1', 'Desc 1', '10/18/2015')
  , ('ID1', 'Desc 2', '10/19/2015')
  , ('ID1', 'Desc 3', '10/22/2015')
  , ('ID1', 'Desc 4', GETDATE())
  , ('ID2', 'Desc 1', '9/18/2015')
  , ('ID2', 'Desc 2', '9/1/2015')
  , ('ID2', 'Desc 3', '9/28/2015')
  , ('ID2', 'Desc 4', GETDATE());

╔═════╦════════╦════════════════╗
║ ID  ║ Desc1  ║ BALANCE_DATE   ║
╠═════╬════════╬════════════════╣
║ ID1 ║ Desc 1 ║ 10/18/2015     ║
║ ID1 ║ Desc 2 ║ 10/19/2015     ║
║ ID1 ║ Desc 3 ║ 10/22/2015     ║
║ ID1 ║ Desc 4 ║ 11/21/2015     ║
║ ID2 ║ Desc 1 ║ 09/18/2015     ║
║ ID2 ║ Desc 2 ║ 09/01/2015     ║
║ ID2 ║ Desc 3 ║ 09/28/2015     ║
║ ID2 ║ Desc 4 ║ 11/21/2015     ║
╚═════╩════════╩════════════════╝

我需要获取除当前日期之外的最新日期行。

预期输出:

╔═════╦════════╦════════════╗
║ ID1 ║ Desc 3 ║ 10/22/2015 ║
║ ID2 ║ Desc 3 ║ 9/28/2015  ║
╚═════╩════════╩════════════╝

如何查询？

Answer 1

WITH cteMaxDateNotCurrent AS (
    SELECT ID, MAX(BALANCE_DATE) AS MaxDate
        FROM tbl
        WHERE BALANCE_DATE <> CAST(GETDATE() AS DATE)
        GROUP BY ID
)
SELECT mdnc.ID, t.Desc1, mdnc.MaxDate
    FROM cteMaxDateNotCurrent mdnc
        INNER JOIN tbl t
            ON mdnc.ID = t.ID
                AND mdnc.MaxDate = t.BALANCE_DATE;