Re: Select only active entries

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.

In response to

Responses

Browse pgsql-novice by date

  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 ?