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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
