NTILE

Question

NTILE

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Ken SSCoach Points: 17191 More actions · Answer 1

I've only ever used NTILE once and it was for a reporting request where they wanted a customer list and only wanted part of the customer base ranked by certain criteria. I don't recall the specific criteria now but basically they wanted customers ranked by income range (per survey data) but they only wanted to see basically the 50-75% range I think. So not the highest income level but not the lowest either. It was marketing data and they wanted to target a certain set of customers. As Tom mentioned, it is a very useful function but only for certain circumstances.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 3

Easy and good one 😀

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 4

Here's a pretty good example with proper explanation on use of NTILE.

http://www.databasejournal.com/features/mssql/article.php/3780311/Exploring-SQL-2005146s-Ranking-Functions-150-NTILE-and-ROWNUMBER.htm

Marcia J SSCertifiable Points: 5649 More actions · Answer 5

Thanks to L'Eomot, KWymore, and ksatpute for commenting on actual uses. That was something I was having a hard time envisioning without examples.

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

raulggonzalez (8/28/2013)
Thanks for the question, it made me scratch my head 🙂
The answers are worded as in BOL, anyway just trying to match the definition with the RANKING functions was a simple guess
the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. --> ROW_NUMBER
the rank of each row within the partition of a result set. After a tie there will numeric gaps in the next ranked number.--> RANK
the rows in an ordered partition into a specified number of groups. --> NTILE
the rank of rows within the partition of a result set, without any gaps in the ranking. --> DENSE_RANK

The best explanation for this question.

+1 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

sqlnaive SSCoach Points: 17437 More actions · Answer 7

sqlnaive

SSCoach

Points: 17437

September 2, 2013 at 1:26 am

#1646679

Simple straightforward question. 🙂

manik_anu SSCrazy Points: 2397 More actions · Answer 8

Hany Helmy (8/31/2013)

raulggonzalez (8/28/2013)
Thanks for the question, it made me scratch my head 🙂
The answers are worded as in BOL, anyway just trying to match the definition with the RANKING functions was a simple guess
the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. --> ROW_NUMBER
the rank of each row within the partition of a result set. After a tie there will numeric gaps in the next ranked number.--> RANK
the rows in an ordered partition into a specified number of groups. --> NTILE
the rank of rows within the partition of a result set, without any gaps in the ranking. --> DENSE_RANK

nice explanation...

Manik
You cannot get to the top by sitting on your bottom.

udayroy15 Ten Centuries Points: 1077 More actions · Answer 9

udayroy15

Ten Centuries

Points: 1077

September 7, 2013 at 5:49 am

#1648314

thanks

jfgoude SSCrazy Points: 2586 More actions · Answer 10

I wonder why Hugo did not answer L'Eomot, is he ill ?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

jfgoude (10/1/2013)
I wonder why Hugo did not answer L'Eomot, is he ill ?

If Tom asked me a question in this topic, I overlooked it. I still don't see it now. Can you point it out for me?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104768 More actions · Answer 12

Hugo Kornelis (10/9/2013)
jfgoude (10/1/2013)
I wonder why Hugo did not answer L'Eomot, is he ill ?
If Tom asked me a question in this topic, I overlooked it. I still don't see it now. Can you point it out for me?

I think he's just used to us disagreeing over trivia now and again. But I can't imagine anything we could disagree about on this question.

Tom