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 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
      ORDER BY
      date ASC,
      name ASC
        • 1. Re: Select top 15 percentile

          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