Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Percentile 99 and Percentile 01

Posted on 2003-10-31
3
Medium Priority
?
357 Views
Last Modified: 2012-05-04
How do I calculate the Percentile 99 and Percentile 01 in the Access Database? I see that we have the PERCENTILE worksheet function, but it takes an array of a maximum of 8191 data points. My need is to calculate the P99 and P01 for more than 60000 data points.
0
Comment
Question by:tmagesh
  • 2
3 Comments
 
LVL 8

Expert Comment

by:HaydnH
ID: 9657670
You can list the top N % using the TOP predicate in SQL... So to calculate the top 1% you would use:

  SELECT TOP 1 PERCENT ScoreField FROM MyTable ORDER BY Score;

And to list the bottom 1% you would use:

  SELECT TOP 1 PERCENT ScoreField FROM MyTable ORDER BY Score DESC;


Haydn.
0
 
LVL 8

Accepted Solution

by:
HaydnH earned 375 total points
ID: 9657806
Sorry, those 2 are the wrong way around - include the DESC to get the TOP 1%, exclude the DESC for the bottom 1%

Haydn.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10012498
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: HaydnH {http:#9657806}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question