X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id DB29C56420
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 26 Feb 2005 13:22:08 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 24435-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 26 Feb 2005 13:22:00 +0000 (GMT)
Received: from quasar.skima.is (quasar.skima.is [212.30.200.205])
	by svr1.postgresql.org (Postfix) with ESMTP id 7C35355A6E
	for <pgsql-sql@postgresql.org>; Sat, 26 Feb 2005 13:21:58 +0000 (GMT)
Received: from gnari ([157.157.175.249] [157.157.175.249]) by quasar.skima.is
	with ESMTP; Sat, 26 Feb 2005 13:22:27 Z
Subject: Re: Read count ?
From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200502241717.14648.aarni@kymi.com>
References: <200502241717.14648.aarni@kymi.com>
Content-Type: text/plain; charset=iso-8859-1
Date: Sat, 26 Feb 2005 13:24:19 +0000
Message-Id: <1109424259.10941.6.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200502/324
X-Sequence-Number: 20434

On Thu, 2005-02-24 at 17:17 +0200, Aarni Ruuhimäki wrote:
> Hi,
> 
> Could someone please give a hint on how to query the following neatly ?
> 
> Get news from a news table that belong to a particular account, get segment 
> name from segments table for each news item and read count from read history 
> table that gets a news_id and timestamp insert every time the news is read. 
> Display everything by news count, most read news first ?
> 
> news_id 4, news_header, segment_name x, read 10 times
> news_id 2, news_header, segment_name y, read 8 times
> news_id 1, news_header, segment_name x, read 7 times
> news_id 3, news_header, segment_name x, read 0 times
> 
> news_table:
> news_id, account_id, segment_id, news_header, ...
> 
> segments_table:
> segment_id, account_id, segment_name
> 
> read_history_table:
> history_id, news_id, timestamp
> 

how about:

select news_id,news_header,segment_name,count(*) 
from news_table 
     natural join segments_table 
     natural join read_history_table
where account_id=? 
group by news_id,news_header,segment_name;

?

gnari