NTILE

Question

NTILE

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

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

tabinsc (8/28/2013)
Good question. One of those code tricks where I go "this is really cool but I can't think of anywhere I would use it".

Try a problem like "List the households with estimated disposable income in the 3rd quartile of disposable household income distribution for households in Cambridge MA" and you'll find NTILE makes life easier.

Tom

Ken SSCoach Points: 17191 More actions · Answer 2

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: 281334 More actions · Answer 3

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 4

Easy and good one 😀

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

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

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 6

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 7

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 8

sqlnaive

SSCoach

Points: 17437

September 2, 2013 at 1:26 am

#1646679

Simple straightforward question. 🙂

manik_anu SSCrazy Points: 2377 More actions · Answer 9

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: 1076 More actions · Answer 10

udayroy15

Ten Centuries

Points: 1076

September 7, 2013 at 5:49 am

#1648314

thanks

jfgoude SSCrazy Points: 2586 More actions · Answer 11

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

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: 104773 More actions · Answer 13

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