Re: Select only active entries

I don't know if it's possible, but you may use OID to accomplish that.

I belive OID is incremental, so if you get the greatest OID from an id, it
should tell if ur row is active or not.

On Wed, Jul 21, 2010 at 3:27 AM, Carel Combrink <s25291930(at)tuks(dot)co(dot)za>wrote:

> Quoting "Thom Brown" <thombrown(at)gmail(dot)com>:
>
>
>> 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
>>
>>
> They are not time stamped but in sequence. The latest active one is
> basically if you look at number 5. It goes from active to inactive to
> active again at time of the query. I want to know if the last entry of ID 5
> was active or inactive. And so-forth for all the rest of the ID's. So only
> select the IDs that were active on their last entry into the database.
>
> Is there a way of querying the data to obtain only the last entry in the
> table for a given ID?
>
>
> --
> Carel Combrink
> s25291930(at)tuks(dot)co(dot)za
>
> This message and attachments are subject to a disclaimer. Please refer
> to www.it.up.ac.za/documentation/governance/disclaimer/ for full
> details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
> onderhewig. Volledige besonderhede is by
> www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>