> 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@tuks.co.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.