Re: Trying to find miss and mister of the last month with highest rating

Hello,

I will do 2 queries -
one for female users (to find "the miss of last month)
and one for males (the "mister of last month").

Here I can fetch all females rated nicely in June:

# select r.id, nice, r.last_rated
from pref_rep r, pref_users u
where r.nice=true and
to_char(current_timestamp - interval '1 month', 'IYYY-MM') =
to_char(r.last_rated, 'IYYY-MM') and
u.female=true and
r.id=u.id;
OK475705800909 | t | 2011-06-15 09:34:29.527786
DE8890 | t | 2011-06-21 14:27:12.442744
OK332253578018 | t | 2011-06-01 01:13:06.767902
OK147226095421 | t | 2011-06-21 11:01:58.151309
VK56919399 | t | 2011-06-25 10:47:52.057593
VK4123791 | t | 2011-06-17 22:44:38.763625
OK259892905389 | t | 2011-06-04 20:12:43.54472
MR13003057189952933403 | t | 2011-06-13 21:38:16.935786

Do you think it's a good query?
(takes few seconds here)

What to do next to pick up
the person having most ratings?

(if there are several persons
having same amount - then I'd like
to pick 1 random - my website is so
obscure, that the users won't notice :-)

And I understand that it would be
most effective to run this query just
once on the 1st of the month,
but I'm too lazy to maintain the
cache files/data, so I want to re-run
query every time the script (actually
going to be a Drupal 7.4 block) runs
(I've switched the hourly block caching on).

Thank you
Alex