Skip site navigation (1) Skip section navigation (2)

Re: Select only active entries

From: "Carel Combrink" <s25291930(at)tuks(dot)co(dot)za>
To: "Thom Brown" <thombrown(at)gmail(dot)com>
Cc: "PostgreSQL Novice List" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Select only active entries
Date: 2010-07-21 06:27:16
Message-ID: 20100721082716.294012coju3l56x0@student.up.ac.za (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-07-21 06:59:48
Subject: Re: Select only active entries
Previous:From: LewDate: 2010-07-21 05:23:38
Subject: Re: Select only active entries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group