-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber Sent: Thursday, July 07, 2011 9:54 AM To: pgsql-general Subject: Trying to find miss and mister of the last month with highest rating # 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 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. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> For the dates you basically need to figure out the correct year, month and day values to represent the prior month using the current month as a base (then build a "date string" and cast it to an actual date). Hint; the last day of the prior month is one day before the first day of the current month. Use a WITH or sub-query to select only ratings between the dates while joining the gender table. You can also perform your "COUNT(*) at this level and group by ID, Gender. In the main query try to use the "RANK()" window function with an ORDER BY on the "count" field and partitioned by "gender". You can probably put this in the HAVING clause and check for "(RANK(*) OVER ...) = 1" Not totally sure on the syntax and don't have time to load up some test data and try different permutations but this should at least get you headed in the right direction if no-one else comes along and provides a more detailed explanation. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
