Carel Combrink wrote:
> I have a table that looks as follow:
>
> =# SELECT id, is_active FROM test_select;
> id | is_active
> ----+-----------
> 5 | t
> 5 | f
> 6 | t
> 7 | t
> 8 | t
> 5 | t
> 8 | f
> 9 | t
> 10 | t
> 6 | f
> 10 | f
> (11 rows)
>
> I want to create a query to select only the latest ones that are active,
"Latest"? There is nothing there to indicate which row is "later" than another.
> is there an easy way to do this or should I rethink my design? The table
Rethink your design. You need at least one column that distinguishes which
rows are "later" than others.
> will not be very big? (I am very new to PostgreSQL and select queries)
This is not a PG-specific matter. It applies to all relational databases.
> I want the following result from a select query:
> =#SELECT id, is_active FROM test_select WHERE <where conditions>;
> id | is_active
> ----+-----------
> 5 | t
> 7 | t
> 9 | t
> (3 rows)
>
> I cant assume that those with uneven number of entries are active since
> one can have the situation where one sets it active and then sets it
> active again and then deactivates it. Or should I rather make sure this
> does not happen and then return all the entries with an uneven number of
> entries? How would such a query look to return the uneven entries?
> I cant use update since I want to retain the history of what happened when.
"When"? Where is "when" shown in your design?
> Any help will be appreciated.
> Using: PostgreSQL 8.4 on Ubuntu 10.04
Tables do not have an order. You must impose that order via ORDER BY on some
column or group of columns. You show no columns in your table design by which
you could establish such an order. As shown, your table cannot support such a
query.
--
Lew