Hi
On 26 July 2010 09:55, Carel Combrink <s25291930@tuks.co.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@gmail.com>