From: | "Carel Combrink" <s25291930(at)tuks(dot)co(dot)za> |
---|---|
To: | andreas(dot)kretschmer(at)schollglas(dot)com |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Select only active entries |
Date: | 2010-07-26 07:55:25 |
Message-ID: | 20100726095525.66135jfz497nq54d@student.up.ac.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> There are no UPDATE or DELETE, just only INSERTs into the table?
You are correct, there are only INSERTS to the table. UPDATE and
DELETE are restricted.
> You *should* consider an additional column, timestamp for instance, the
trick with ctid isn't a clean solution.
My table is defined as follow:
=# CREATE TABLE test_select
(
id integer,
is_active boolean,
idx serial NOT NULL,
CONSTRAINT test_select_pkey PRIMARY KEY (idx)
);
and the complete table looks like:
=# SELECT * FROM test_select;
id | is_active | idx
----+-----------+-----
5 | t | 1
5 | f | 2
6 | t | 3
7 | t | 4
8 | t | 5
5 | t | 6
8 | f | 7
9 | t | 8
10 | t | 9
6 | f | 10
10 | f | 11
(11 rows)
How would the query look based on the 'idx' column?
IF I replace 'ctid' with 'idx' I get:
=# select id, is_active from (select id, is_active, max(idx),
row_number() over (partition by id) from test_select group by id,
is_active, idx order by id, idx desc) foo where is_active and
row_number = 1;
id | is_active
----+-----------
5 | t
6 | t
7 | t
9 | t
10 | t
(5 rows)
Or if I must use a timestamp column how would I structure the query?
--
Carel Combrink
s25291930(at)tuks(dot)co(dot)za
This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
From | Date | Subject | |
---|---|---|---|
Next Message | Luiz Eduardo Cantanhede Neri | 2010-07-26 12:45:07 | Re: Select only active entries |
Previous Message | Sandeep | 2010-07-25 07:48:08 | Which CMS/ecommerce/shopping cart works with Postgres ? |