Thread: Read count ?
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 TIA, Aarni -------------- This is a bugfree broadcast to you from **Kmail** on **Fedora Core 2** linux system --------------
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
Did some searching on forums archives to no avail, I found a PL/Perl example but would like to do this in plpgsql if possible. Does somebody have this sitting around, I just dont want to reinvent the wheel. But if need to I will.
Thanks in advance,
Oisin
create or replace function reverse_string(text) returns text as
'
DECLARE
reversed_string text;
incoming alias for $1;
BEGIN
reversed_string =3D '''';
for i in reverse char_length(incoming)..1 loop
reversed_string =3D reversed_string || substring(incoming from i for 1);
end loop;
return reversed_string;
END'
language plpgsql;
select * from reverse_string('Postgres');
reverse_string
sergtsoP
On Feb 26, 2005, at 11:55 AM, Oisin Glynn wrote:
> Did some searching on forums archives to no avail, I found a=A0=20
> PL/Perl=A0example but would like to do this in plpgsql if possible. Does=
=20
> somebody have this sitting around, I just dont want to reinvent the=20
> wheel.=A0 But if need to I will.
> =A0
> Thanks in advance,
> =A0
> Oisin
>
Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com
On Saturday 26 February 2005 11:55 am, Oisin Glynn wrote: > Did some searching on forums archives to no avail, I found a PL/Perl > example but would like to do this in plpgsql if possible. Does somebody > have this sitting around, I just dont want to reinvent the wheel. But if > need to I will. > > Thanks in advance, > > Oisin CREATE FUNCTION reverse(text) RETURNS text AS $_$ DECLARE original alias for $1; reverse_str text; i int4; BEGIN reverse_str := ''; FOR i IN REVERSE LENGTH(original)..1 LOOP reverse_str := reverse_str || substr(original,i,1); END LOOP; RETURN reverse_str; END;$_$ LANGUAGE plpgsql IMMUTABLE; from a posting a while back.
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.