Re: Select only active entries

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.