Re: Select only active entries - Mailing list 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@gmail.com> wrote:
>
> On Mon, Jul 26, 2010 at 10:03 AM, Rob Richardson
> <Rob.Richardson@rad-con.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice