Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
March 20th, 2013, 06:46 AM
 Lakana
Registered User

Join Date: Mar 2013
Posts: 1
Time spent in forums: 10 m 42 sec
Reputation 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
March 20th, 2013, 12:30 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,823
Time spent in forums: 3 Months 1 Week 4 Days 8 h 51 m 7 sec
Reputation Power: 4208
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

#3
March 20th, 2013, 01:14 PM
 jemagee
Contributing User

Join Date: Jan 2009
Posts: 41
Time spent in forums: 7 h 9 m 6 sec
Reputation Power: 9
Here's a question I have - can you use 'math' in your limit statement.

I.e. limit (Select count(*) from table * .15)?

#4
March 20th, 2013, 01:15 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,823
Time spent in forums: 3 Months 1 Week 4 Days 8 h 51 m 7 sec
Reputation Power: 4208
Quote:
 Originally Posted by jemagee can you use 'math' in your limit statement.
what happened when you tested it?

#5
March 20th, 2013, 01:20 PM
 jemagee
Contributing User

Join Date: Jan 2009
Posts: 41
Time spent in forums: 7 h 9 m 6 sec
Reputation Power: 9
Quote:
 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`

 Viewing: Dev Shed Forums > Databases > MySQL Help > Select top 15 percentile