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

Re: Select only active entries

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Select only active entries
Date: 2010-07-21 05:23:38
Message-ID: i2608q$5ak$1@news.albasani.net (view raw or flat)
Thread:
Lists: pgsql-novice
Carel Combrink wrote:
> I have a table that looks as follow:
>
> =# SELECT id, is_active FROM test_select;
> id | is_active
> ----+-----------
> 5 | t
> 5 | f
> 6 | t
> 7 | t
> 8 | t
> 5 | t
> 8 | f
> 9 | t
> 10 | t
> 6 | f
> 10 | f
> (11 rows)
>
> I want to create a query to select only the latest ones that are active,

"Latest"?  There is nothing there to indicate which row is "later" than another.

> is there an easy way to do this or should I rethink my design? The table

Rethink your design.  You need at least one column that distinguishes which 
rows are "later" than others.

> will not be very big? (I am very new to PostgreSQL and select queries)

This is not a PG-specific matter.  It applies to all relational databases.

> I want the following result from a select query:
> =#SELECT id, is_active FROM test_select WHERE <where conditions>;
> id | is_active
> ----+-----------
> 5 | t
> 7 | t
> 9 | t
> (3 rows)
>
> I cant assume that those with uneven number of entries are active since
> one can have the situation where one sets it active and then sets it
> active again and then deactivates it. Or should I rather make sure this
> does not happen and then return all the entries with an uneven number of
> entries? How would such a query look to return the uneven entries?
> I cant use update since I want to retain the history of what happened when.

"When"?  Where is "when" shown in your design?

> Any help will be appreciated.
> Using: PostgreSQL 8.4 on Ubuntu 10.04

Tables do not have an order.  You must impose that order via ORDER BY on some 
column or group of columns.  You show no columns in your table design by which 
you could establish such an order.  As shown, your table cannot support such a 
query.

-- 
Lew

In response to

Responses

pgsql-novice by date

Next:From: Carel CombrinkDate: 2010-07-21 06:27:16
Subject: Re: Select only active entries
Previous:From: Ashwa Inc.Date: 2010-07-20 21:39:39
Subject: C Postgresql CGI

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