Re: Read count ?

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