Re: Complex sql, limit-for-each group by, arrays, updates

I'll probably go by using 3 queries and putting them in a transaction.

Thanks

On Wed, Nov 27, 2013 at 5:38 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> Dorian Hoxha wrote
> > Hi,
> >
> > So i have (table where data will be read) :
> > CREATE TABLE data (vid,cid,pid,number);
> >
> > Tables where data will be writen/updated:
> >
> > CREATE TABLE pid_top_vids (pid, vid[])
> > CREATE TABLE pid_top_cids (pid, cid[])
> > CREATE TABLE cid_top_vids (cid, vid[])
> >
> > I need to , possibly in 1 query, this will run once in a while:
> > Get top(10) vids , sorted by 'number',grouped by pid and update the row
> in
> > TABLE(pid_top_vids).
> >
> > Get top(10) vids, sorted by 'number', grouped by cid and update the row
> in
> > TABLE(cid_top_vids).
> >
> > Get top(10) cids, sorted by 'number', where number is the SUM() of each
> > vid
> > GROUP_BY(cid) and update the row in TABLE (cid_top_vids);
> >
> > So, get data, create sorted array, and update the rows (they exist, so
> > only
> > the arrays have to be updated).
> >
> > Possible ?
> > Thanks
>
> I would not bother trying to do it in a single SQL statement but each query
> is possible, and fairly basic, using UPDATE with a FROM clause containing
> the desired sub-query.
>
> David J.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Complex-sql-limit-for-each-group-by-arrays-updates-tp5780554p5780599.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>