I just think about another solution:
Add another column as bit or bool to indicate if that row is active.
id | is_active | valid_row
5 | t | f
5 | f | f
6 | t | f
7 | t | t
8 | t | f
5 | t | t
8 | f | t
9 | t | t
10 | t | f
6 | f | t
10 | f | t
to get result as you want
SELECT id, is_active FROM foo WHERE is_valid = t AND valid_row = t
and before you insert an item, UPDATE foo set valid_row = f WHERE id = @id.
But yet timestamp or bigserial would give you less effort.
On Mon, Jul 26, 2010 at 2:51 PM, Rodrigo E. De León Plicet
> On Mon, Jul 26, 2010 at 10:03 AM, Rob Richardson
> <Rob(dot)Richardson(at)rad-con(dot)com> wrote:
> > Carel Combrink wrote:
> >> 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,
> > It is very poor design to assume that records in a database have any order
> > whatsoever. You are setting yourself up for some very hard-to-find bugs
> > that way. If you merely add a column named "insert_time" of type timestamp
> > and set its default value to "now()", you will have a guaranteed way to know
> > the order in which records were inserted, and you don't have to change any
> > query that references your table. Or, you can recreate the table with a
> > column of type "bigserial". That will automatically set up a sequence that
> > will number the records in the order in which they were inserted.
> > HTH,
> > RobR
> Yes, Carel really needs to add date/time information to the schema,
> but one column is not recommended, you need two; refer to the
> following URL to see why:
> Joe Celko's thinking in sets: auxiliary, temporal, and virtual tables in SQL
> Page 162
> 9.2.2 Single Timestamp Tables
> URL: http://tinyurl.com/2b2g6dx ( goes to http://books.google.com )
> Celko explains these and many other subjects better than most people;
> I recommend reading the whole book.
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-novice by date
|Next:||From: Joshua Tolley||Date: 2010-07-26 21:18:32|
|Subject: Re: Missing Functions|
|Previous:||From: Rodrigo E. De León Plicet||Date: 2010-07-26 17:51:50|
|Subject: Re: Select only active entries|