Re: Select only active entries

Rob,

I know we could use a time stamp to do the job, but I think I read in his
thread that he didn't want to redesign, so that's why I suggest using OID.
But if I were him i'd put timestamp on table as you suggested.

On Mon, Jul 26, 2010 at 12:59 PM, Michael Wood <esiotrot(at)gmail(dot)com> wrote:

> Hi
>
> On 26 July 2010 09:55, Carel Combrink <s25291930(at)tuks(dot)co(dot)za> wrote:
> >
> >> 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?
>
> No, you don't need a timestamp if you have your idx column. It serves
> the same purpose.
>
> Try this:
>
> SELECT id, is_active FROM test_select AS a INNER JOIN (SELECT max(idx)
> AS idx FROM test_select WHERE is_active GROUP BY id) AS b ON a.idx =
> b.idx;
>
> By the way, why bother returning the is_active column if it's always
> going to contain 'true'?
>
> Maybe you want:
>
> SELECT id FROM test_select AS a INNER JOIN (SELECT max(idx) AS idx
> FROM test_select GROUP BY id) AS b ON a.idx = b.idx ORDER BY id;
>
> --
> Michael Wood <esiotrot(at)gmail(dot)com>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>