Re: Select only active entries

On 20 July 2010 14:44, Carel Combrink <s25291930(at)tuks(dot)co(dot)za> wrote:
> Hi,
>
> 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, is
> there an easy way to do this or should I rethink my design? The table will
> not be very big? (I am very new to PostgreSQL and select queries)
>
> 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.
>
> Any help will be appreciated.
> Using: PostgreSQL 8.4 on Ubuntu 10.04
>

But what do you regard as being the "latest"? Do you have a timestamp
or sequence column?

And could you provide more information about what this data represents?

Thom