On 20 July 2010 14:44, Carel Combrink <s25291930@tuks.co.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