Re: Trying to find miss and mister of the last month with highest rating - Mailing list pgsql-general

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