從 NTILE 返回第一行或最後一行

是否可以從 SQL SERVER 中的 NTILE 分區返回第一行（或最後一行）？

例如，在這個小提琴上，我希望每個組的最後一個成員沒有逗號。

SQL 小提琴： http ://sqlfiddle.com/#!18/15223/4

你可以這樣做：

WITH your_mom
   AS (   SELECT   T.ID, CAST(T.ID AS VARCHAR) + ',' AS [FORMATED ID], NTILE(3) OVER ( ORDER BY T.ID ) AS [GROUP]
          FROM     #TEST AS T )
SELECT  *
FROM    your_mom AS b
WHERE   b.ID = (   SELECT   MAX(b2.ID)
                  FROM     your_mom AS b2
                  WHERE    b2.[GROUP] = b.[GROUP] );

這是否涵蓋了您想要的結果：

SELECT TOP 1 *
FROM (
     SELECT T.ID,  CAST(T.ID AS VARCHAR) + ',' AS [FORMATED ID],
            NTILE(3) OVER(ORDER BY T.ID) AS [GROUP]
     FROM TEST T
     ) X
ORDER BY ID, [GROUP];

SqlFiddle在這裡

引用自：https://dba.stackexchange.com/questions/197274