SQL Server 2012 FIRST_VALUE 函數實戰：分組首值、排序影響與 LAST_VALUE 對比

FIRST_VALUE 回傳視窗中第一列的指定運算式值，受 OVER (ORDER BY ...) 控制「第一列」定義。本文示範基本用法、排序方向影響、PARTITION BY 分組首值、與 LAST_VALUE 的關係。

測試資料 (同 LEAD 範例)

DECLARE @TestData TABLE (
    ID INT IDENTITY(1,1), Department VARCHAR(20), LastName VARCHAR(20), Rate FLOAT
);
-- 插入 11 筆測試資料...

案例 1：全域首值 (依 ID 遞增)

SELECT
    ID, Department, LastName,
    FIRST_VALUE(LastName) OVER (ORDER BY ID) AS FirstUser
FROM @TestData;

所有列的 FirstUser 均為 'Arifin' (ID=1 的 LastName)。

案例 2：排序方向影響 (依 ID 遞減)

SELECT
    ID, Department, LastName,
    FIRST_VALUE(LastName) OVER (ORDER BY ID DESC) AS FirstUserDesc
FROM @TestData;

所有列的 FirstUserDesc 均為 'Meyyappan' (ID=11 的 LastName)。

案例 3：分組首值 (PARTITION BY Department)

SELECT
    Department, LastName,
    FIRST_VALUE(LastName) OVER (PARTITION BY Department ORDER BY ID) AS FirstInDept
FROM @TestData;

結果：

• Document Control 群組：所有列首值 = 'Arifin' (該群組 ID 最小)。
• Information Services 群組：所有列首值 = 'Chai' (該群組 ID 最小)。

案例 4：分組內排序方向影響

SELECT
    Department, LastName,
    FIRST_VALUE(LastName) OVER (PARTITION BY Department ORDER BY ID DESC) AS LastInDept
FROM @TestData;

各群組首值變為該群組 ID 最大者 (Document Control → 'Kharatishvili', Information Services → 'Meyyappan')。

關於 LAST_VALUE

LAST_VALUE 對應取得視窗中「最後一列」的值，但預設視窗框架為 RANGE UNBOUNDED PRECEDING AND CURRENT ROW，導致直接使用時回傳「目前列」而非真正最後一列。

正確使用 LAST_VALUE

SELECT
    ID, LastName,
    LAST_VALUE(LastName) OVER (
        ORDER BY ID
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS TrueLastValue
FROM @TestData;

必須顯式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 擴展視窗至完整分區。

FIRST_VALUE vs LAST_VALUE 對比

應用場景

• 基準比較：Rate / FIRST_VALUE(Rate) OVER (PARTITION BY Dept ORDER BY Date) AS RateRatio
• 首筆記錄標記：CASE WHEN ID = FIRST_VALUE(ID) OVER (...) THEN 1 ELSE 0 END
• 分組排序首值傳播：將群組第一筆的設定值套用至全群組。

FIRST_VALUE 直觀易用，LAST_VALUE 需注意視窗框架陷阱，兩者搭配 PARTITION BY 能解決多數「群組首/尾值傳播」需求。