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 F09FD54507
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Feb 2005 13:08:31 +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 85480-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Feb 2005 13:08:28 +0000 (GMT)
Received: from smtp10.kymp.net (smtp10.kymp.net [80.248.96.136])
	by svr1.postgresql.org (Postfix) with ESMTP id 069C852AA6
	for <pgsql-sql@postgresql.org>; Mon, 28 Feb 2005 13:08:26 +0000 (GMT)
Received: from [192.168.16.100] (static-241-66.kymp.net [213.130.241.66])
	by smtp10.kymp.net (8.13.3/8.13.3) with SMTP id j1SD8R7k012112;
	Mon, 28 Feb 2005 15:08:28 +0200
From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni@kymi.com>
Reply-To: aarni@kymi.com
Organization: KYMI.com
To: Ragnar =?iso-8859-1?q?Hafsta=F0?= <gnari@simnet.is>
Subject: Re: Read count ?
Date: Mon, 28 Feb 2005 15:08:27 +0200
User-Agent: KMail/1.6.2
References: <200502241717.14648.aarni@kymi.com>
	<1109424259.10941.6.camel@localhost.localdomain>
In-Reply-To: <1109424259.10941.6.camel@localhost.localdomain>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200502281508.27291.aarni@kymi.com>
X-Virus-Scanned: by amavisd-new
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200502/356
X-Sequence-Number: 20466

Hi,

Thanks Ragnar. This, and an other GROUP BY + name query within output got m=
e=20
there.

<cfquery name=3D"get_news" datasource=3D"#ds#">=20
SELECT DISTINCT news_id, news_header, segment, segment_id, count(*)=20
=46ROM news_table=20
NATURAL JOIN segments_table
NATURAL JOIN read_history=20
WHERE account_id =3D #Url.account_id#=20
GROUP BY news_id, news_header, segment, segment_id ORDER BY count DESC=20
</cfquery>
=2E..
<cfoutput query=3D"get_news" group=3D"news_id">=20
  =20
<cfquery name=3D"get_seg" datasource=3D"#ds#">=20
SELECT segment_name
=46ROM segments_table=20
WHERE segment_id =3D #segment#=20
   =20
</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=E4ki 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=3D?
> group by news_id,news_header,segment_name;
>
> ?
>
> gnari
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


=2D-------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
=2D-------------
Linux is like a wigwam - no windows, no gates and an apache inside.