0

How can I use the ntile ranking function without specifying the number to divide each partition by and instead assign each partition an incrementing number?

I want to group items that have a subset of fields with the same values and then flag them all with the same number such that each group's number increments by one in sorted order.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
H.T
  • 21
  • 1
  • 7
  • Not very clear what are you after,,,,some sample data and desired output will help a lot. – M.Ali Aug 19 '15 at 21:48

1 Answers1

2

NTILE isn't your function. You want DENSE_RANK.

select DENSE_RANK() over (Order by Col1, Col2...) GroupFlag,
    * 
from MyTable

This would give all records with common values in fields Col1 and Col2 the same integer. The GroupFlag integers will start with 1 and count up in the order of the order by clause.

Let me know if you intended to ask something else and I'll try again.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • Thanks for your reply its exactly what i want but can I do this without change the order of the data @Brian Pressler – H.T Aug 30 '15 at 07:11
  • can you please check the previous comment if there is a way to do this. – H.T Aug 30 '15 at 07:37
  • You can add an `order by` clause after the `from` clause to order the records in any way that you like... it won't affect the numbering of `GroupFlag`. – Brian Pressler Aug 31 '15 at 14:37