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

Do you think this query is good?
(or is it allocating loads
of strings for the month comparisons?)

# select r.id, count(r.id)
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
group by r.id
order by count desc
limit 7;
       id       | count
----------------+-------
 OK348033534186 |    49
 OK145143239265 |    46
 OK4087658302   |    41
 DE11370        |    36
 DE11467        |    36
 OK351488505084 |    35
 OK524565727413 |    33
(7 rows)

(I'll just change "limit 7" to "limit 1"
above to pick the "miss of ls month")

And why can't I add u.name, u.avatar
to fetch all the info I need in 1 pass?

# select r.id, count(r.id), u.name, u.avatar, u.city
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
group by r.id
order by count desc
limit 7;
ERROR:  column "u.*" must appear in the GROUP BY clause or be used in
an aggregate function
LINE 1: select r.id, count(r.id), u.name, u.avatar, u.city
                                  ^

Is there a way to workaround it?

Regards
Alex