Lists: | pgsql-sql |
---|
From: | Aarni Ruuhimäki <aarni(at)kymi(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Read count ? |
Date: | 2005-02-24 15:17:14 |
Message-ID: | 200502241717.14648.aarni@kymi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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
--------------
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 |
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: | "Oisin Glynn" <me(at)oisinglynn(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Reverse String in sql or pgplsql |
Date: | 2005-02-26 17:55:11 |
Message-ID: | 006f01c51c2c$54fb6ef0$a974fea9@homisco.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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
From: | Larry Rosenman <ler(at)lerctr(dot)org> |
---|---|
To: | "Oisin Glynn" <me(at)oisinglynn(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Reverse String in sql or pgplsql |
Date: | 2005-02-26 19:05:39 |
Message-ID: | 200502261305.40170.ler@lerctr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.
From: | Bradley Miller <bmiller(at)nuvio(dot)com> |
---|---|
To: | "Oisin Glynn" <me(at)oisinglynn(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Reverse String in sql or pgplsql |
Date: | 2005-02-26 20:05:42 |
Message-ID: | 3177ec80719b1221976bd55c5a9bde44@nuvio.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
create or replace function reverse_string(text) returns text as
'
DECLARE
reversed_string text;
incoming alias for $1;
BEGIN
reversed_string = '''';
for i in reverse char_length(incoming)..1 loop
reversed_string = 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
> 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
>
Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller(at)nuvio(dot)com
From: | Aarni Ruuhimäki <aarni(at)kymi(dot)com> |
---|---|
To: | Ragnar Hafstað <gnari(at)simnet(dot)is> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Read count ? |
Date: | 2005-02-28 13:08:27 |
Message-ID: | 200502281508.27291.aarni@kymi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.