SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

Ranking Functions Part 2 (NTILE)

A friend of mine recently told me she’s very proud of her son, because he is consistently in the upper quarter of every class he takes. Right there she performed a calculation similar to the NTILE function. She didn’t know it, but she tiled the class into four pieces and then identified which piece her son belongs in.

Just like its three fellow ranking functions, NTILE takes data you’ve sorted and then marches straight down the list in order of row number. Instead of assigning a rank value to each record, NTILE counts records and assigns them into a group (a.k.a., “tile”, or “bucket” in geek speak) based on:
1) the number of tiles/pieces you have specified, and
2) the count of records contained in the query.

Like all other ranking functions, we want to first write a SELECT statement and an ORDER BY clause to determine the sort order.

Solarwinds

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Ranking Functions - Advanced NTILE in Detail - Day 13 of 35 j2p_13_1

Adding the NTILE function to the query is almost the same as with the first three ranking functions we saw from yesterdays blog post. The ORDER BY clause gets plugged into the parentheses of the OVER( ) clause. Just one new item to note with the NTILE:  you must insert the number of tiles (or pieces) in the parentheses of the NTILE. In this case we want four pieces, just like my friend’s story about her son.

The upper quarter consists of Grants 007, 008, 009 and are the “1” group of records. The lowest quarter (the group “4” records) consists of Grants 001 and 010 as seen in the figure below

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Ranking Functions - Advanced NTILE in Detail - Day 13 of 35 j2p_13_2

If you want just an upper and lower half ranking just change this query to show just two pieces. We see the grant records divided into an upper half (the “1” records) and a lower half (“2” records). The figure below shows the top 5 grants in group 1 and the lowest 5 grants in group 2. In this case the NTILE query now divides the grants into an upper half and lower half.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Ranking Functions - Advanced NTILE in Detail - Day 13 of 35 j2p_13_3

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter7.0Setup.sql script from Volume 2.

Question 13

You want to find the top 2% of all students’ Grade Point Averages (GPA). Which NTILE would you use?

  1. NTILE(1) OVER(ORDER BY GPA DESC)
  2. NTILE(2) OVER(ORDER BY GPA DESC)
  3. NTILE(25) OVER(ORDER BY GPA DESC)
  4. NTILE(50) OVER(ORDER BY GPA DESC)

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35

Related Posts

56 Comments. Leave new

  • Correct answer is #4 (NTILE(50)). If we want 2%, it means 100/50 = 2%.
    So, we need to create 50 groups in order to find the top 2%. Our top 2% will be in the very first group.

    So, the query will be
    select * from (select *, NTILE(50) OVER (ORDER BY GPA DESC) as Grp
    from StudentRates) X where Grp = 1

    Alternative query will be

    select top 2 percent * from StudentRates ORDER BY GPA DESC

    I am from USA

    Reply
  • Jason P. Brown (@bensdad03)
    August 14, 2011 7:38 am

    The correct answer would be 4 because you would want to divide it into 50 equal pieces which would be in 2% increments.

    Reply
  • MOHAMMED ZUHEB
    August 14, 2011 10:16 am

    Correct answer is #4 (NTILE(50)). If we want 2%, it means 100/50 = 2%.
    So, we need to create 50 groups in order to find the top 2%. Our top 2% will be in the very first group.

    So, the query will be
    select * from (select *, NTILE(50) OVER (ORDER BY GPA DESC) as Grp
    from StudentRates) X where Grp = 1

    Alternative query will be

    select top 2 percent * from StudentRates ORDER BY GPA DESC

    Reply
  • Hi Pinal,
    The correct option is 4.
    Explanation- As the requirement is to find the top 2% of all students’ Grade Point Averages (GPA) which can be achieved using NTILE(50) OVER(ORDER BY GPA DESC) based on the assumption that there are more than 50 records in the table.

    Thanks,
    Manik
    Country-INDIA

    Reply
  • NTILE(50) OVER(ORDER BY GPA DESC)

    Chetan
    Country: USA

    Reply
  • Q 13) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35

    A.) NTILE(1) will return a record set consisting of 1 group that contains 100% of the records so (1) is not correct. NTILE(2) will split the result set into 2 groups with one group having the upper 50 % of the grades and the other group having the lower 50% (2) is also incorrect. Since NTILE(25)’s result set will consist of 25 groups each containing 4% of the grades (3) is also wrong. Because splitting 100% of the grades into 50 equal size groups will create groups that each contain 2% of the grades, NTILE (50) as shown in (4) is the correct answer.

    Winner from USA: Naomi

    Winner from India: Varinder Sandhu

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • Option 4:
    NTILE(50) OVER(ORDER BY GPA DESC)

    Will group all the rows in 50 groups i.e 50 groups of 2% each
    kkmishra
    india

    Reply
  • option 4 is correct

    NTILE(50) OVER(ORDER BY GPA DESC)

    Rajesh Garg
    India

    Reply
  • Uday Kumar B R
    August 16, 2011 12:44 pm

    Answer is :

    4.NTILE(50) OVER(ORDER BY GPA DESC)

    Country : India

    Reply
  • Answer is 4
    NTILE(50) OVER(ORDER by GPA DESC)

    Country : India

    Reply
  • Hi

    Correct Answer 4. NTILE(50) OVER(ORDER BY GPA DESC)

    we need 50 parts so each part would represent 2% of the Column

    Somnath Desai
    India

    Reply
  • Rajesh Mohanrangan
    August 16, 2011 4:44 pm

    Answer is 4

    NTILE(50) OVER(ORDER BY GPA DESC)

    From India

    Reply
  • Uday Bhoopalam
    August 16, 2011 11:31 pm

    Option 4 is the correct answer.

    Uday
    USA

    Reply
  • Nikhil Mahajan
    August 17, 2011 8:22 am

    Correct Answer is : Option 4

    Because 2% means 100/2 Parts ie..50.

    So answer is no4- NTILE(50) OVER(ORDER BY GPA DESC) is correct Answer

    india

    Reply
  • Correct Answer ::: NTILE(50) OVER(ORDER BY GPA DESC)

    Reply

Leave a Reply

Menu