August 28, 2013 at 11:00 am
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
August 28, 2013 at 12:24 pm
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.
August 28, 2013 at 7:22 pm
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
August 28, 2013 at 11:33 pm
Easy and good one 😀
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
August 29, 2013 at 2:11 am
Here's a pretty good example with proper explanation on use of NTILE.
August 29, 2013 at 3:00 pm
Thanks to L'Eomot, KWymore, and ksatpute for commenting on actual uses. That was something I was having a hard time envisioning without examples.
August 31, 2013 at 3:22 am
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
September 2, 2013 at 1:26 am
Simple straightforward question. 🙂
September 4, 2013 at 5:03 am
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.
September 7, 2013 at 5:49 am
thanks
October 1, 2013 at 4:03 am
I wonder why Hugo did not answer L'Eomot, is he ill ?
October 9, 2013 at 2:25 pm
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?
October 9, 2013 at 4:23 pm
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)
You must be logged in to reply to this topic. Login to reply