Magic2K2
March 4th, 2002, 14:17
I'm trying to find the percentile of each record in a query using MySQL, but I've found no function that can do this. What I want basically is:
SELECT member_id, PERCENTILE() AS tile FROM members ORDER BY fantasy DESC
In this case, PERCENTILE() is my made up function that should equal some number between 0 and 100 (or 0 and 1) that represents the percentile that the field fantasy relates to the rest of the query.
Example of records:
member_id, fantasy
1, 34
2, 23
3, 89
4, 74
In this example, I'd want to find 25 for member_id = 2, 50 for member_id = 1, 75 for member_id = 4, and 100 for member_id = 3
The problem is that I have over 1000 records to deal with and need an easy way to get this.
Thanks in advance for your help.
SELECT member_id, PERCENTILE() AS tile FROM members ORDER BY fantasy DESC
In this case, PERCENTILE() is my made up function that should equal some number between 0 and 100 (or 0 and 1) that represents the percentile that the field fantasy relates to the rest of the query.
Example of records:
member_id, fantasy
1, 34
2, 23
3, 89
4, 74
In this example, I'd want to find 25 for member_id = 2, 50 for member_id = 1, 75 for member_id = 4, and 100 for member_id = 3
The problem is that I have over 1000 records to deal with and need an easy way to get this.
Thanks in advance for your help.