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

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

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.

*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?

- NTILE(1) OVER(ORDER BY GPA DESC)
- NTILE(2) OVER(ORDER BY GPA DESC)
- NTILE(25) OVER(ORDER BY GPA DESC)
- 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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference: Pinal Dave (**http://blog.SQLAuthority.com****)**

Correct answer is option 2.

New Delhi

For this question: Question 13

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

I believe the answer is

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

Dawn (USA)

Correct answer is No. 4, because each tile contains 2% of all students’ Grade Point Averages (GPA).

Rene Castro

El Salvador

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

2/100 = 1/50

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

Leo Pius

USA

We are looking at 2% – 1/50th of the entire populace. This needs 50 tiles to be created. Hence, option 4 (NTILE(50)) is appropriate.

Ramakrishnan RS

Mysore, India

correct answer is:(4) NTILE(50) OVER(ORDER BY GPA DESC)

kkmishra

india

Question 13

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

Chennai, INDIA

The correct answer is #4 – i.e. to use NTILE(50) OVER (ORDER BY GPA DESC)

Reason: I don’t have a database to work this out, but based on pure deduction, 1/50 = 0.02, i.e. 2%.

Country of residence: India

Correct answer:4

NTILE(50) OVER(ORDER BY GPA DESC)

INDIA

Brijesh

Correct Option is 4.

Shilpa

India

Option 4 is Correct

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

India

The Correct Answer is : 4 (NTILE(50) OVER(ORDER BY GPA DESC))

— GVPrabu

Bangalore || India

Correct Answer is : Option 4

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

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

— GVPrabu

Bangalore || India

Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

Correct Answer is:4

Thanks,

Basavaraj

India

option 4

Hi,

Option 4 is correct.

Regards,

Sudhir Chawla

New Delhi, India

Correct Answer is : Option 4

(Sale, Nigeria)

Blog Question have correct option :4

NTILE(50) OVER(ORDER BY GPA DESC)

It will be get the top 2 in a piece.

Chennai, TamilNadu, India

Hi Sir,

The correct answer is Option no 4

Option 1 :

NTILE(1) OVER(ORDER BY GPA DESC)

Will group all the rows in a single group. i.e 100%

Option 2 :

NTILE(2) OVER(ORDER BY GPA DESC)

Will group all the rows in two groups. i.e two groups of 50% each

Option 3:

NTILE(25) OVER(ORDER BY GPA DESC)

Will group all the rows in 25 groups i.e 25 groups of 4% each.

Option 4:

NTILE(50) OVER(ORDER BY GPA DESC)

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

so after this query the first group is the top 2% of all students’ Grade Point Averages (GPA).

So the correct option is 4.

P.Anish Shenoy,

INDIA

Hi All,

Correct answer is 4 as bellow

NTILE(50) OVER(ORDER BY GPA DESC)

I am from India

Option 4:

NTILE(50) OVER(ORDER BY GPA DESC)

This Query will return 50 groups with 2% data in each group.

The first group will have top 2% of all students’ Grade Point Averages (GPA).

Thanks,

Shree

Bangalore India

Correct answer is 4. i.e..NTILE(50) OVER(ORDER BY GPA DESC)

Here we are looking for 2%, whihc is one of the 50 tiles/parts.

I’m from UK

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

Reason: You want to find the top 2% of all students’ Grade Point Averages (GPA). So, we need to divide into 50 parts and then only we get 100/50 = 2%.

Thanks and regards

Ritam Banerjee

India

I think the option 4 is correct as

NTILE(50) OVER(ORDER BY GPA DESC)

Varinder Sandhu (India)

Correct answer is 4

i.e. NTILE(50) OVER(ORDER by GPA DESC)

I’m from india

Correct Answer is “NTILE(50) OVER(ORDER BY GPA DESC)”

ie. Option 4 is correct.

Rajneesh Verma

(INDIA)

4.

Specified 50 tiles to split data out to. That would reach the goal of 2%

Dan

New Jersey USA

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

Gordon Kane

Allen TX

USA

Correct answer is option 4.

Top two percent refers to 1/50 records so ntile (50) will give right answer .

Sumit

India

Correct answer:4

NTILE(50) OVER(ORDER BY GPA DESC)

INDIA

lalit

Answer is:

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

Vinay

Pune, India

Answer is option 4.

NTILE(50) OVER(ORDER BY GPA DESC)

Country : India

correct answer : option no 4

shekhar gurav.

country : India

Hi Pinal Sir,

The Correct Option for the above question is Option 4.

Explanation :

Here we need to find the top 2% of all students’ Grade Point Averages (GPA).

Here the Interesting thing to note is that when we specify the number in NTILE() Function it divided the total records into that many sets of records:

Ex: 2 Means 50 % Expression (No of records /2 = we will get 2 sets)

So we will get 2 sets. Say suppose for the % sake we need to take 100% (i.e.) total Number* 1 /2

Total * .5.

Now Our requirement is 2% so 2 / 100 = 1/total number

That gives us 100/2 = 1 /50

Hence the number is 50 (groups)

Hence the correct option is 4) NTILE(50) OVER(ORDER BY GPA DESC)

Why Other Options are wrong:

Option 1) NTILE(1) OVER(ORDER BY GPA DESC)

This Option will group all the records mathematically speaking 100% records so ruled out.

Option 2) NTILE(2) OVER(ORDER BY GPA DESC)

This Option will group all the records into half mathematically speaking 50% records in one group so ruled out.

Option 3) NTILE(25) OVER(ORDER BY GPA DESC)

Above query will group all the records into 25 groups mathematically speaking 4% records in one group.

Hence the correct Option is 4.

and a very happy independece Day to you and all my country people!!!!

DILIP KUMAR JENA

Country : INDIA

Hi

I would select answer 4. NTILE(50) OVER(ORDER BY GPA DESC)

1/50 – we need 50 parts so each part would represent 2% of the students

Hi

I would select answer 4. NTILE(50) OVER(ORDER BY GPA DESC)

1/50 – we need 50 parts so each part would represent 2% of the students

Eric

USA

Ans is:

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

Partha

India

Option is 4 is corrected only based on below assumptions.

My assumption for the following question is highest GPA is 100.

Assuming there are all the GPA from 1 to 100. And there 100 or multiples of 100 rows only.

There some problems which might rise with this option also, like scenarios where everyone has scored 100.

Or more then 2% of them have scored 100.

INDIA

The correct answer is:

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

This will divide the results into groups of 2%.

Thanks for this great contest.

Country: United States

Hi Pinal,

Challenge:

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)

Correct Answer:

The correct answer is #4:

NTILE(50) OVER(ORDER BY GPA DESC)

Explanation:

Choice #1 will have only 1 tile, meaning all results will be in the tile. This isn’t what was requested.

Choice #2 will have only 2 tiles, segmenting the upper 50% from the lower 50%. This isn’t what was requested.

Choice #3 will have 25 tiles, segmenting the results into groups of 4%. This isn’t what was requested.

Choice #4 will have 50 tiles, segmenting the results into groups of 2%. This is what was requested and is the correct answer.

Country:

United States

Thanks for the knowledge!

Regards,

Bill Pepping

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

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

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

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

NTILE(50) OVER(ORDER BY GPA DESC)

Chetan

Country: USA

Q 13) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35A.) 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:

NaomiWinner from India:

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

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

option 4 is correct

NTILE(50) OVER(ORDER BY GPA DESC)

Rajesh Garg

India

Answer is :

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

Country : India

Answer is 4

NTILE(50) OVER(ORDER by GPA DESC)

Country : India

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

Answer is 4

NTILE(50) OVER(ORDER BY GPA DESC)

From India

Option 4 is the correct answer.

Uday

USA

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

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

Pingback: SQL SERVER – SQL Query Techniques For Microsoft SQL Server 2008 – Book Available for SQL Server Certification Journey to SQLAuthority

Pingback: SQL SERVER – Identity Fields – Contest Win Joes 2 Pros Combo (USD 198) – Day 2 of 5 « SQL Server Journey with SQL Authority

Pingback: SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198) « SQL Server Journey with SQL Authority

Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave