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)


    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 🙂

  • Simple straightforward question. 🙂

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

  • 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 28 (of 28 total)

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