# NTILE

• 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

• 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.

• 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

• Easy and good one 😀

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

• 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

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

• raulggonzalez (8/28/2013)

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

• Simple straightforward question. 🙂

• Hany Helmy (8/31/2013)

raulggonzalez (8/28/2013)

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.

• thanks

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

• 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/

• 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

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