Re: Select only active entries

> 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.