THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

T-SQL thoughts about the 95th percentile

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, August 23, 2011 5:37 PM by Rob Farley



RichB said:

Being lazy I just take the top 5 percent ordered desc, and take the top 1 off that ordered asc.


select top 1 * from (select top 5 percent * from table ordered by val desc) ordered by val asc

Sure, it's ugly, but it's easy :)

August 23, 2011 6:42 AM

Rob Farley said:

Yeah, there are definitely a bunch of ways you can work out what you count as your 95th. This method was as much an exercise in picturing a solution as anything, and trying to be sure of how things work when you don't have a nice round number.

August 23, 2011 7:16 AM

Alex Whittles said:

Hi Rob

Very interesting post and a great technique, thanks for sharing it.

The difference between your approach and any technique using stddev is essentially whether you want the top % of a sample or of the entire population. Stddev takes the limited sample data available and projects the distribution characteristics of the much larger unknown population.

Both techniques are equally valid, and as you pointed out, depend on what definition of percentile is required. I.e. Do you have all available data or just a sample of it.

Given this, hopefully you should avoid a bashing from the stats folk!

August 24, 2011 3:19 AM

Rob Farley said:

Ah, true. And hopefully statisticians won't mind my method either. Thanks Alex.

August 24, 2011 3:47 AM

Geoff said:

What I did at one point in time in algorithm/psuedo-code form

-get the total number of the set

-get the ideal index (0.95 * N)

-get the entry right below that index and right above

-take the value of the one below

-take the difference of the one below and above

-add the fractional difference based on the index between the two numbers to the lower value

I hope that makes sense. If I want greater speed I just round and use the top function or row_number functions.

August 25, 2011 10:14 AM

Rob Farley said:

Yes, that's the algorithm I applied here. But you have to make sure you subtract one and add it back again afterwards, because if you have just two numbers, you have one range between those two. Your point should be 1.95 not 1.9.


August 25, 2011 11:20 AM

Lars Hammarberg said:

"95% of statisticians may be offended at this post"

Hilarious! Thank you!

/Lars H

August 29, 2011 3:20 PM

Rob Farley said:

SQL 2012 brings us a bunch of new analytic functions , together with enhancements to the OVER clause

April 9, 2012 9:53 PM

Ara said:

This is very useful.

Many thanks.

August 8, 2013 4:42 PM

Perastikos said:

Hello guys, that looks really interesting

my question is , if i want to use the final percentile function which part of code should i use?

thanks in advanced

February 3, 2016 7:39 AM

Rob Farley said:

Then change the .975 to .99

February 3, 2016 7:53 AM

Perastikos said:

Mr. Rod are u answering to me?

February 3, 2016 8:03 AM

Rob Farley said:

Yes, Perastikos, I am.

February 3, 2016 8:08 AM

Perastikos said:

Sorry i gou confused,

i have table with these values



and i want to take percentile of 90 of these values, which picture of your code should i execute?

i want to manage same think like excel, that uses this formula :


February 3, 2016 8:10 AM

Rob Farley said:

Use the second-last one, and change the 0.975 value to whatever you want.

February 3, 2016 5:54 PM

Perastikos said:

Hello again, i run the code from second last picture, i have those two values in my table,

5.04799 and 6.11519

i want to take 90% and 10% percentile of these values, when i run the code twice for 90% and 10% i take exactly the same values in rangetop :


in excel i take for 90% : 6% and 10% : 5%

any help?

February 4, 2016 2:28 AM

sccsc said:


December 27, 2016 7:31 AM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG


Privacy Statement