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

Re: Select only active entries

From: Rodrigo E(dot) De León Plicet <rdeleonp(at)gmail(dot)com>
To: Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org, Carel Combrink <s25291930(at)tuks(dot)co(dot)za>
Subject: Re: Select only active entries
Date: 2010-07-26 17:51:50
Message-ID: AANLkTimpi1tj95sLLNyZwD6U5eVJw7HKpd2COe8RHw-1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

Responses

pgsql-novice by date

Next:From: Luiz Eduardo Cantanhede NeriDate: 2010-07-26 18:14:24
Subject: Re: Select only active entries
Previous:From: Luiz Eduardo Cantanhede NeriDate: 2010-07-26 17:19:04
Subject: Re: Select only active entries

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