0

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 7 '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 8 '18 at 4:13
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] );
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

  • no it is not giving correct output, "..last member from each group without the comma." – KumarHarsh Feb 8 '18 at 4:11
0
;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) as rn_asc,
       row_number() over(partition by [group] order by id) as rn_desc
from cte
)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.