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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Trying to find miss and mister of the last month with highest rating
Date: 2011-07-07 13:54:12
Message-ID: CAADeyWjoSFPnTmmZx3aMe0TctmWwQZ_yN7Fmcr+0vUESwuppPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

at my website users can rate each other:

# select id, nice, last_rated from pref_rep where nice=true
order by last_rated desc limit 7;
id | nice | last_rated
------------------------+------+----------------------------
OK152565298368 | t | 2011-07-07 14:26:38.325716
OK452217781481 | t | 2011-07-07 14:26:10.831353
OK524802920494 | t | 2011-07-07 14:25:28.961652
OK348972427664 | t | 2011-07-07 14:25:17.214928
DE11873 | t | 2011-07-07 14:25:05.303104
OK335285460379 | t | 2011-07-07 14:24:39.062652
OK353639875983 | t | 2011-07-07 14:23:33.811986

And I know their gender:

# select id, female from pref_users limit 7;
id | female
----------------+--------
OK351636836012 | f
OK366097485338 | f
OK251293359874 | t
OK7848446207 | f
OK335478250992 | t
OK355400714550 | f
OK146955222542 | t

I'm trying to construct 2 queries -
one to find the female user with
highest count of ratings for the last month
(not just for the last 30 days -
and this condition is already killing me)
and the same for non-female users.

Any help please? SQL is so hard sometimes.

Regards
Alex,

using PostgreSQL 8.4.8 / CentOS 5.6

Responses

Browse pgsql-general by date

  From Date Subject
Next Message akp geek 2011-07-07 13:55:45 Oracle to Postgres migration open source tool
Previous Message vincent dephily 2011-07-07 13:34:19 DELETE taking too much memory