From: | Ragnar Hafstað <gnari(at)simnet(dot)is> |
---|---|
To: | aarni(at)kymi(dot)com |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Read count ? |
Date: | 2005-02-26 13:24:19 |
Message-ID: | 1109424259.10941.6.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | AL ELK | 2005-02-26 16:09:02 | Re: triggers |
Previous Message | Tom Lane | 2005-02-25 15:51:53 | Re: Are SQL queries locale dependent? |