A-Original-Client-UID: 188.129.71.72 splivalo
Message-ID: <4CF8CC26.70206@megafon.hr>
Date: Fri, 03 Dec 2010 11:53:26 +0100
From: Mario Splivalo <mario.splivalo@megafon.hr>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.15) Gecko/20101027 Lightning/1.0b1 Thunderbird/3.0.10
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Get the max viewd product_id for user_id
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

I have a log-table where I record when some user_id has viewed some 
product_id:

CREATE TABLE viewlog (
	user_id integer,
	product_id integer,
	view_timestamp timestamp with time zone
)

Now, I would like to get result that gives me, for each user_id, 
product_id of the product he/she viewed the most time, with the number 
of views.

The 'issue' is I need this running on postgres 8.0.

I went this way, but for a large number of user_id's, it's quite slow:

CREATE VIEW v_views AS
	SELECT user_id, product_id, count(*) as views
	FROM viewlog
	GROUP BY user_id, product_id

SELECT
	DISTINCT user_id,
	(SELECT product_id FROM v_views inn WHERE inn.user_id = out.user_id 
ORDER BY views DESC LIMIT 1) as product_id,
	(SELECT views FROM v_views inn WHERE inn.user_id = out.user_id ORDER BY 
views DESC LIMIT 1) as views
FROM
	v_views out


	Mario