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

How do you determine what is the first and what is the last element of a group? Based on id value? Greater is ID, greater is its number in a group? — sepupic, Feb 07 '18 at 14:07
I have never seen good use of NTile.I wonder what you are upto ?what is your real problem like ? — KumarHarsh, Feb 08 '18 at 04:13

score 1 · Answer 1 · answered Feb 07 '18 at 14:11

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

score 1 · Answer 2 · answered Feb 07 '18 at 14:14

1

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

answered Feb 07 '18 at 14:14

McNets

23,749
10
48
88

no it is not giving correct output, "..last member from each group without the comma." – KumarHarsh Feb 08 '18 at 04:11

score 0 · Answer 3 · edited Jul 15 '21 at 18:14

;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

Return 1st or last row from NTILE

3 Answers3

Linked