Re: Select only active entries

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>