Re: Select only active entries

Carel Combrink wrote:
> I want to create a query to select only the latest ones that are active,
...
>> 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)
>> );
...
>> 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?

Michael Wood wrote:
> No, you don't need a timestamp if you have your idx column. It serves
> the same purpose.

Until it wraps around.

--
Lew