Return 1st or last row from NTILE

Question

Is is possible to return the first (or last) row from a NTILE partition in SQL SERVER?

For example, on this fiddle I wish I could have the last member from each group without the comma.

SQL Fiddle: http://sqlfiddle.com/#!18/15223/4

Answer 1

You could do something like this:

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

Answer 2

Does this cover your desired result:

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 here

Answer 3

;with cte as
(
SELECT
  T.ID,
  CAST(T.ID AS VARCHAR) + ',' AS [FORMATED ID],
  NTILE(3) OVER(ORDER BY T.ID) AS [GROUP]  
 FROM
  TEST T
  )

,cte1 as 
(
select *, 
       row_number() over(partition by [group] order by id asc) as rn_asc,
       row_number() over(partition by [group] order by id desc) as rn_desc
from cte
)

select id
from cte1
where rn_asc = 1 -- rn_desc = 1 for last element