Requires Free Membership to View
I will answer your question using standard SQL, so more than just the Oracle users who read this can benefit. Aside from the NUMBER data type, the SQL in my example should be portable.
A percentile is a value on a scale of one hundred that indicates the percent of a distribution that is equal to or below it. In SQL, for every row, we want to find the number of rows that have values less than or equal to its value. That count, divided by the count of all the rows, is the percentile. This is best illustrated with an example. Suppose we have the results of a college exam stored in a table:
create table ExamResults
( Student varchar(15),
Score number(3)
);
insert into ExamResults values ('Lenny',94);
insert into ExamResults values ('Ralph',93);
insert into ExamResults values ('Joe',91);
insert into ExamResults values ('Mary',80);
insert into ExamResults values ('Frank',89);
insert into ExamResults values ('Susan',89);
insert into ExamResults values ('Bill',74);
insert into ExamResults values ('Ben',99);
insert into ExamResults values ('Fred',66);
insert into ExamResults values ('George',100);
insert into ExamResults values ('Walter',90);
insert into ExamResults values ('Betty',89);
insert into ExamResults values ('Heather',83);
insert into ExamResults values ('Richard',91);
commit;
We could get our percentiles with SQL like this:select a.*, round( 100.0 * ( select count(*) from ExamResults b where b.Score <= a.Score ) / total.cnt , 1) percentile from ExamResults a cross join ( select count(*) cnt from ExamResults ) total;Here are the results ordered by score in descending order:
STUDENT SCORE PERCENTILE --------------- ---------- ---------- George 100 100 Ben 99 92.9 Lenny 94 85.7 Ralph 93 78.6 Joe 91 71.4 Richard 91 71.4 Walter 90 57.1 Frank 89 50 Susan 89 50 Betty 89 50 Heather 83 28.6 Mary 80 21.4 Bill 74 14.3 Fred 66 7.1The cross join allows us to take the count of all rows and use it in a calculation on every row. Because the sub-query always returns one row, the cross join will return one and only one row per row found in the ExamResults table. If you are using a SQL engine that does not support SQL-92 joins, you should be able to replace the "cross join" in the statement with a comma and get the same results. Since you are using Oracle, I should note that Oracle 8i does not support SQL-92 joins, however, 9i does.
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation