Re: [SQL] pagination problem in postgresql need help
select aiah_number.aiah_number_id, aiah_number.aiah_number,
...
order by rank_value desc limit 1 offset 1;
I use:
==
select ...
order by ...
LIMIT #{pageSize}::INTEGER OFFSET #{offset}::INTEGER;
Emi
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Query to select nested comments sorted by nesting and date
I am trying to select nested commentes from a table with this structure CREATE TABLE picture_comments ( comment_id serial NOT NULL, user_id integer NOT NULL, "comment" text NOT NULL DEFAULT ''::text, comment_date timestamp without time zone NOT NULL DEFAULT now(), ipaddr inet NOT NULL, reply_to integer NOT NULL DEFAULT 0, -- ID of parent comment_id. 0 for comments that are not replies to other comments deleted smallint NOT NULL DEFAULT 0, id_tree ltree NOT NULL DEFAULT ''::ltree, -- ltree structure of comment IDs 1.2.3.4, etc. reply_date timestamp with time zone DEFAULT now(), -- comment_date of most recent reply (of any depth under it). pid integer NOT NULL, -- Picture ID } The result needs to be sorted by date of most recent reply descending (replying bumps the thread) but also need to be sorted such that the parent/child relationships are maintained. Multiple replies on the same level also need to be sorted by date desc. Getting the parent/child sorting can be accomplished with a simple "order by id_tree", but I can't find any way to combine that with date sorting without breaking the nesting. What is the most efficient way of making this work? I exparimented with "WITH RECURSIVE" but it won't allow me to sort until the end so it doesn't seem to help. Plus it appears to be much slower than just using the ltree (100ms for ltree based vs 1.5 seconds for WITH RECURSIVE). I could do the sorting in the php code, but it seems more efficient if I can just do it all in a single SQL query.
Re: [SQL] Append n Rows into a Single Row
On Sat, Jun 18, 2011 at 08:51:55PM -0700, Samuel Gendler wrote: > Actually, you need the array_agg() function to aggregate multiple rows into > a single array, and that is discussed on the aggregate functions page, > here: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html > > You could then use one of the other array functions to manipulate that > array. > > But given that you are appending strings to each other in your example, you > can also just use the string_agg function from that same page - assuming you > are on 9.0. It looks like that function isn't available before 9.0 > Nice blog post about this here: http://www.postgresonline.com/journal/archives/191-stringagg.html Ross -- Ross Reedstrom, Ph.D. [email protected] Systems Engineer & Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] problem with selecting from a function
Hi, I've got a table with a couple of objects. Primary key object_id. There is a function that fetches some values from another table that relate to an object_id. Like fctX ( 7 ) --> set of typX ... ( 14, 'bla' ), ( 17, 'blu' ), ( 21, 'ble' ), ... The result of the function can have 0 or more lines of a defined result-type typX. Those resulting numbers are not object_ids. Now I'd need a SELECT that lists all function results of all object_ids. Like: ... 6, ... 7, 14, 'bla' 7, 17, 'blu' 7, 21, 'ble' 8, ... Actually it was enough to get just the numerical column of the function result. I tried select object_id, fctX (object_id) from objects; Then I get: 7, (14, 'bla') 7, (17, 'blu') 7, (21, 'ble') <--- round brackets This looks like an array but how can I split it up to columns or at least extract the number-column? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem with selecting from a function
2011/6/21 Andreas : > Hi, > > I've got a table with a couple of objects. > Primary key object_id. > > There is a function that fetches some values from another table that relate > to an object_id. > Like fctX ( 7 ) --> set of typX ... ( 14, 'bla' ), ( 17, 'blu' ), ( 21, > 'ble' ), ... > The result of the function can have 0 or more lines of a defined result-type > typX. > Those resulting numbers are not object_ids. > > Now I'd need a SELECT that lists all function results of all object_ids. > Like: > ... > 6, ... > 7, 14, 'bla' > 7, 17, 'blu' > 7, 21, 'ble' > 8, ... > > Actually it was enough to get just the numerical column of the function > result. > > I tried > select object_id, fctX (object_id) from objects; > Then I get: > 7, (14, 'bla') > 7, (17, 'blu') > 7, (21, 'ble') <--- round brackets > This looks like an array but how can I split it up to columns or at least > extract the number-column? > this is composite value you can try SELECT object_id, (fctX(object_id)).* from objects Regards Pavel Stehule > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
