From: | Luiz Eduardo Cantanhede Neri <lecneri(at)gmail(dot)com> |
---|---|
To: | postgresql novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Select only active entries |
Date: | 2010-07-26 18:14:24 |
Message-ID: | AANLkTimk4dnogrp0bJ1ULj=5uPEEbMjE+f74Y-tNCcR2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I just think about another solution:
Add another column as bit or bool to indicate if that row is active.
Exemple
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
<rdeleonp(at)gmail(dot)com> wrote:
>
> 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.
>
> Regards.
>
> --
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Tolley | 2010-07-26 21:18:32 | Re: Missing Functions |
Previous Message | Rodrigo E. De León Plicet | 2010-07-26 17:51:50 | Re: Select only active entries |