1 Reply Latest reply: Mar 21, 2013 2:51 AM by Marwim RSS

    Select top 15 percentile

    998021
      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
          Marwim
          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