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