#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    1
    Rep Power
    0

    Select top 15 percentile


    I have 3 columns

    Code:
    Name, Date, Rate
    ===
    A, 07-02-2012, 4.7
    B, 07-02-2012, 5.3
    ....
    I want to print 5 columns

    Code:
    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

    Code:
    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    let's first of all confirm that you are actually using mysql (which does not have a TOP keyword) and not microsoft sql server (which does, but not with PERCENTILE)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Here's a question I have - can you use 'math' in your limit statement.

    I.e. limit (Select count(*) from table * .15)?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by jemagee
    can you use 'math' in your limit statement.
    what happened when you tested it?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Originally Posted by jemagee
    Here's a question I have - can you use 'math' in your limit statement.

    I.e. limit (Select count(*) from table * .15)?
    Turns out can't - but can do this

    From stackoverflow

    Code:
    SELECT* FROM    (     SELECT list.*, @counter := @counter +1 AS counter     FROM (select @counter:=0) AS initvar, list     ORDER BY value DESC    ) AS X where counter <= (10/100 * @counter); ORDER BY value DESC

IMN logo majestic logo threadwatch logo seochat tools logo