On 24/08/2011, at 4:44 PM, Chris Hanks wrote:
> Thanks Royce. I put together another query using a WITH statement
> that's also working:
>
> WITH v AS (
> SELECT item_id, type, direction, array_agg(user_id) as user_ids
> FROM votes
> WHERE root_id = 5305
> GROUP BY type, direction, item_id
>
Thanks Royce. I put together another query using a WITH statement
that's also working:
WITH v AS (
SELECT item_id, type, direction, array_agg(user_id) as user_ids
FROM votes
WHERE root_id = 5305
GROUP BY type, direction, item_id
ORDER BY type, direction, item_id
)
SELECT *,
(SELECT use
This might help you:
http://www.postgresql.org/docs/8.4/static/queries-with.html
On 24/08/2011, at 9:54 AM, Chris Hanks wrote:
> I have two tables:
>
> CREATE TABLE items
> (
> root_id integer NOT NULL,
> id serial NOT NULL,
> -- Other fields...
>
> CONSTRAINT items_pkey PRIMARY KEY (root_
I have two tables:
CREATE TABLE items
(
root_id integer NOT NULL,
id serial NOT NULL,
-- Other fields...
CONSTRAINT items_pkey PRIMARY KEY (root_id, id)
)
CREATE TABLE votes
(
root_id integer NOT NULL,
item_id integer NOT NULL,
user_id integer NOT NULL,
type smallint NOT NULL,
