Retrieving top 100 results 1

[vik1ng]

Hi. I am trying to get an average value from my database but I only want to retrieve the average out of the top 100 results. The column I want to average contains values from 0.1 to 10.0.
So I want to get the average from the top 100 values in this column. The average syntax is clear but what do I have to add?
This is what I have now:
$query="select AVG(M_RATING) as rating FROM rate_members WHERE M_COUNTRY='SE'";
But this gives me an average based on all values in the column.

I can't figure out how to do this. Even I guess there is an easy answer

 

[TonyGroves]

You would use a sub-select (assuming you are using MySQL 4.1 or later):
[tt]
select AVG(m_rating) rating
FROM
(
SELECT m_rating
FROM rate_members
WHERE m_country='SE'
ORDER BY r_rating desc
LIMIT 100
)
[/tt]

 

[vik1ng]

Thanks. Man..
But I am on version 3.23.58

Is there no way then?

 

[TonyGroves]

Then I suppose a temporary table would be in order:
[tt]
CREATE TEMPORARY TABLE t AS
SELECT m_rating
FROM rate_members
WHERE m_country='SE'
ORDER BY r_rating desc
LIMIT 100;

SELECT AVG(m_rating) rating FROM t;
[/tt]
process data ...
[tt]
DROP TABLE t;
[/tt]

 

[vik1ng]

I guess this would be a disaster for performance?

 

[TonyGroves]

Not necessarily. The table would probably be created in memory only, which would be fast.

I suppose it's a case of try it and see what happens.