NTILE

  • ksatpute123

    Hall of Fame

    Points: 3325

    Comments posted to this topic are about the item NTILE

  • Bangla

    Hall of Fame

    Points: 3137

    Easy and nice.....

  • Koen Verbeeck

    SSC Guru

    Points: 258905

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • ksatpute123

    Hall of Fame

    Points: 3325

    Thank YOU all for the encouragement.

    The SQL bug bite is just beginning to take effect for me. 🙂

  • Carlo Romagnano

    SSC-Insane

    Points: 21710

    the rows in an ordered partition into a specified number of groups

    In the answer a word is missing "Distributes".

    So, NTILE returns the number of the group to which the row belongs

    and not the number of the rows in an ordered partition into a specified number of groups.

  • Mighty

    SSCrazy Eights

    Points: 8398

    Sorry, but I think the answers are not worded correctly (so there is no correct answer in that list), so I selected the one that seemed least incorrect. And I was wrong of course.

    The so called correct answer "the rows in an ordered partition into a specified number of groups" seems to be incorrect to me. It doesn't state that it is the number of the group to which the row belongs that is returned, but the number of rows in a specific partition.

    The description from Technet is much clearer: "For each row, NTILE returns the number of the group to which the row belongs."

    I want my point back.

  • ksatpute123

    Hall of Fame

    Points: 3325

    I don't know how to give points back but I will keep in mind what you said the next time I post a question.

    Still I think this is pretty clear. NTILE returns:

    "the rows in an ordered partition into a specified number of groups."

    Thanks for the feedback.

  • RandomEvent

    SSCrazy

    Points: 2963

    Nice and easy if stuck the BOL is a good place to check out.

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104762

    I think that the correct answer is badly worded. Certainly it is correct, whereas that's clearly impossible for each of the other three options. But it uses the word "partition" in one sense (the whole set presented along with a partitioning of it), while the same word is used in its other, rather more common, sense (one of the subsets into which the whole sets is partitioned) in each of the three incorrect answers; if it meant partition in the latter sense, it would be incorrect. In context with the other three options, it's natural to assume that partition has its more common meaning so that tends to confuse people.

    Since the other three options are so utterly wrong and incapable of being interpreted as anything anywhere near correct, this shouldn't have caused anyone any real problem though.

    Anyway, MS named this wrong; ntile should have been nthtile, after all we say quartile not quattile :hehe:

    Tom

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    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

  • ksatpute123

    Hall of Fame

    Points: 3325

    WOW... Thanks for organizing it so neatly. Exactly what the question was intended for.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Nice simple question. Don't be put off by the comments about improving the wording. They just want to help you make a better question the NEXT time you offer one. 😉

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    Again, the question serves its purpose of making me investigate and learn something - but the wording just isn't clear. I read the very same article that is linked before selecting my answer and still had difficulty translating the right answer into one of the available options.

    "Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs."

  • tabinsc

    SSCommitted

    Points: 1812

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

    Tony
    ------------------------------------
    Are you suggesting coconuts migrate?

Viewing 15 posts - 1 through 15 (of 28 total)

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