From: Aarni Ruuhimäki <aarni@kymi.com>
To: Ragnar Hafstað <gnari@simnet.is>
Cc: pgsql-sql@postgresql.org
Subject: Re: Read count ?
Date: Mon, 28 Feb 2005 15:08:27 +0200
Message-ID: <200502281508.27291.aarni@kymi.com> (raw)
In-Reply-To: <1109424259.10941.6.camel@localhost.localdomain>
References: <200502241717.14648.aarni@kymi.com>
<1109424259.10941.6.camel@localhost.localdomain>
Hi,
Thanks Ragnar. This, and an other GROUP BY + name query within output got me
there.
<cfquery name="get_news" datasource="#ds#">
SELECT DISTINCT news_id, news_header, segment, segment_id, count(*)
FROM news_table
NATURAL JOIN segments_table
NATURAL JOIN read_history
WHERE account_id = #Url.account_id#
GROUP BY news_id, news_header, segment, segment_id ORDER BY count DESC
</cfquery>
...
<cfoutput query="get_news" group="news_id">
<cfquery name="get_seg" datasource="#ds#">
SELECT segment_name
FROM segments_table
WHERE segment_id = #segment#
</cfquery>
#news_header# - #get_seg.segment_name# - #count#
</cfoutput>
On Saturday 26 February 2005 15:24, you wrote:
> 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, 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------
Linux is like a wigwam - no windows, no gates and an apache inside.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aarni@kymi.com, gnari@simnet.is
Subject: Re: Read count ?
In-Reply-To: <200502281508.27291.aarni@kymi.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox