1 Reply Latest reply on Mar 21, 2013 7:51 AM by Marwim

# Select top 15 percentile

I have 3 columns
``````Name, Date, Rate
===
A, 07-02-2012, 4.7
B, 07-02-2012, 5.3
....``````
I want to print 5 columns
``````Name, Date, Rate, 15th percentile rate, 85th percentile rate
==
A, 07-02-2012, 4.7, ?, ?
B, 07-02-2012, 5.3, ?, ?
...``````
So the two new columns should be the 15th and 85th percentile of the rate at a given date. In other words, it should list the banks at each date by rate and pick the 15 percent lowest rate and 15 percent highest rate at each date. Does anyone know how to handle this?

I guess it's something like
``````SELECT
name,
date,
rate,
(SELECT TOP 15 PERCENTILE rate WHERE date = '?' ORDER BY rate ASC LIMIT 1) AS rate_1,
(SELECT TOP 15 PERCENTILE rate WHERE date = '?' ORDER BY rate DESC LIMIT 1) AS rate_2
FROM
rate_table
ORDER BY
date ASC,
name ASC``````
• ###### 1. Re: Select top 15 percentile
Hello,

welcome to the forum.

This is the forum for the tool {forum:id=260}. Please mark this post as answered so other users know that they can ignore it. Then post again in {forum:id=75}.

You will enhance your chance to get a good answer, when you provide your sample data as a create table and insert statement or as a with clause like
``````WITH sample_data AS (
SELECT 'A' name, DATE '2012-02-07' dates, 4.7 rate FROM dual UNION ALL
SELECT 'B' name, DATE '2012-02-07' dates, 5.3 rate FROM dual
)
SELECT * FROM sample_data;

NAME DATES            RATE
---- ---------- ----------
A    07.02.2012        4.7
B    07.02.2012        5.3``````
Regards
Marcus