Anyway to calculate a percentile?

Has anyone had the need to calculate a percentile in Oracle 7.3.x? For example if I had a table with a numerical column how would I find the value at the 10th percentile? I can think of several ways I could try this, none of which I like, so I thought I might throw it up for grabs here.

The first idea I had involved ordering the list and dividing the rownum by the row count to derive a percentile. But there's apparently a problem with doing that. This approach would be based in part on the following SQL:

select rownum, col1
from

	(select col1
	from table1
	order by col1) ordered_list

When I add the order by clause to the subselect I get a compilation error. ORA-00907: missing right parenthesis. I can almost understand that it makes no sense to to order a subselect like that (except that that's key to my solution), but why would I get a compilation error?

Anyway, if anyone's had any experience doing something like this, or has any ideas your help would be greatly appreciated.

Thanks,
Rob

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jul 11 2000 - 00:00:00 CDT