Обсуждение: Select only active entries
Hi, 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, is there an easy way to do this or should I rethink my design? The table will not be very big? (I am very new to PostgreSQL and select queries) 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. Any help will be appreciated. Using: PostgreSQL 8.4 on Ubuntu 10.04 -- Carel Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
On 20 July 2010 14:44, Carel Combrink <s25291930@tuks.co.za> wrote: > Hi, > > 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, is > there an easy way to do this or should I rethink my design? The table will > not be very big? (I am very new to PostgreSQL and select queries) > > 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. > > Any help will be appreciated. > Using: PostgreSQL 8.4 on Ubuntu 10.04 > But what do you regard as being the "latest"? Do you have a timestamp or sequence column? And could you provide more information about what this data represents? Thom
Quoting "Thom Brown" <thombrown@gmail.com>: > > On 20 July 2010 14:44, Carel Combrink <s25291930@tuks.co.za> wrote: >> Hi, >> >> 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, is >> there an easy way to do this or should I rethink my design? The table will >> not be very big? (I am very new to PostgreSQL and select queries) >> >> 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. >> >> Any help will be appreciated. >> Using: PostgreSQL 8.4 on Ubuntu 10.04 >> > > But what do you regard as being the "latest"? Do you have a timestamp > or sequence column? > > And could you provide more information about what this data represents? > > Thom > 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 Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
In response to Carel Combrink : > > 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? There are no UPDATE or DELETE, just only INSERTs into the table? If yes, maybe this should work for you: test=*# select * 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) test=*# select id, is_active from (select id, is_active, max(ctid), row_number() over (partition by id) from test_selectgroup by id, is_active, ctid order by id, ctid desc) foo where is_active and row_number = 1; id | is_active ----+----------- 5 | t 7 | t 9 | t (3 rows) You *should* consider an additional column, timestamp for instance, the trick with ctid isn't a clean solution. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
Hello all, I have a table with id and status_v. create table myt (id integer, status_v varchar(10)); insert into myt values (1,'I'); insert into myt values (1,'P'); insert into myt values (1,'T'); insert into myt values (2,'P'); User gets counts of status records, filtered on some status_v like this test=# select count(*), status_v from myt where status_v in ('I','P','X') group by status_v; count | status_v -------+---------- 1 | I 2 | P (2 rows) Is there a way in which I can have the output as 1 | I 2 | P 0 | X i.e., if there is not data, the count should be displayed as 0. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
First of all, please don't hijack other threads. In response to Jayadevan M : > Hello all, > > I have a table with id and status_v. > > create table myt (id integer, status_v varchar(10)); > insert into myt values (1,'I'); > insert into myt values (1,'P'); > insert into myt values (1,'T'); > insert into myt values (2,'P'); > > User gets counts of status records, filtered on some status_v like this > test=# select count(*), status_v from myt where status_v in ('I','P','X') > > group by status_v; > count | status_v > -------+---------- > 1 | I > 2 | P > (2 rows) > > Is there a way in which I can have the output as > 1 | I > 2 | P > 0 | X > > i.e., if there is not data, the count should be displayed as 0. test=*# select * from myt; id | status_v ----+---------- 1 | I 1 | P 1 | T 2 | P (4 rows) test=*# select foo.status_v, count(myt.status_v) from myt right join (select unnest(array['I','P','X']) as status_v) fooon foo.status_v=myt.status_v group by foo.status_v; status_v | count ----------+------- X | 0 I | 1 P | 2 (3 rows) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
> First of all, please don't hijack other threads. Oops, sorry. I replied to a mail and "changed the subject". Is it still 'hijacking'? I thought change of subject will make it a different thread. Thanks for the solution. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
In response to Jayadevan M : > > First of all, please don't hijack other threads. > Oops, sorry. I replied to a mail and "changed the subject". Is it still > 'hijacking'? I thought change of subject will make it a different thread. Yeah, the mail still contains the References-Header ... > > Thanks for the solution. You are welcome. -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > [...] > test=*# select foo.status_v, count(myt.status_v) from myt right join (select unnest(array['I','P','X']) as status_v) fooon foo.status_v=myt.status_v group by foo.status_v; ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > status_v | count > ----------+------- > X | 0 > I | 1 > P | 2 > (3 rows) You can also write this as "(VALUES ('I'), ('P'), ('X')) AS foo (status_v)" which I personally find more readable (con- stant vs. function). Tim
In response to Tim Landscheidt : > "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > > > [...] > > test=*# select foo.status_v, count(myt.status_v) from myt right join (select unnest(array['I','P','X']) as status_v)foo on foo.status_v=myt.status_v group by foo.status_v; > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > status_v | count > > ----------+------- > > X | 0 > > I | 1 > > P | 2 > > (3 rows) > > You can also write this as "(VALUES ('I'), ('P'), ('X')) AS > foo (status_v)" which I personally find more readable (con- > stant vs. function). You are right, thx. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
Jayadevan M wrote: >> First of all, please don't hijack other threads. > Oops, sorry. I replied to a mail and "changed the subject". Is it still > 'hijacking'? I thought change of subject will make it a different thread. This is exactly the "normal" definition of hijacking. As is too often the case, Microsoft has a different definition; MS products sometimes treat a new subject as a new thread - this can be very annoying when you fix a spelling error in subject!!
> There are no UPDATE or DELETE, just only INSERTs into the table? You are correct, there are only INSERTS to the table. UPDATE and DELETE are restricted. > You *should* consider an additional column, timestamp for instance, the trick with ctid isn't a clean solution. My table is defined as follow: =# CREATE TABLE test_select ( id integer, is_active boolean, idx serial NOT NULL, CONSTRAINT test_select_pkey PRIMARY KEY (idx) ); and the complete table looks like: =# SELECT * FROM test_select; id | is_active | idx ----+-----------+----- 5 | t | 1 5 | f | 2 6 | t | 3 7 | t | 4 8 | t | 5 5 | t | 6 8 | f | 7 9 | t | 8 10 | t | 9 6 | f | 10 10 | f | 11 (11 rows) How would the query look based on the 'idx' column? IF I replace 'ctid' with 'idx' I get: =# select id, is_active from (select id, is_active, max(idx), row_number() over (partition by id) from test_select group by id, is_active, idx order by id, idx desc) foo where is_active and row_number = 1; id | is_active ----+----------- 5 | t 6 | t 7 | t 9 | t 10 | t (5 rows) Or if I must use a timestamp column how would I structure the query? -- Carel Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
I don't know if it's possible, but you may use OID to accomplish that.
I belive OID is incremental, so if you get the greatest OID from an id, it should tell if ur row is active or not.
I belive OID is incremental, so if you get the greatest OID from an id, it should tell if ur row is active or not.
On Wed, Jul 21, 2010 at 3:27 AM, Carel Combrink <s25291930@tuks.co.za> 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.Quoting "Thom Brown" <thombrown@gmail.com>:
On 20 July 2010 14:44, Carel Combrink <s25291930@tuks.co.za> wrote:Hi,
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, is
there an easy way to do this or should I rethink my design? The table will
not be very big? (I am very new to PostgreSQL and select queries)
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.
Any help will be appreciated.
Using: PostgreSQL 8.4 on Ubuntu 10.04
But what do you regard as being the "latest"? Do you have a timestamp
or sequence column?
And could you provide more information about what this data represents?
Thom
Is there a way of querying the data to obtain only the last entry in the table for a given ID?
--This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
--Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
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
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 Combrink
Hi On 26 July 2010 09:55, Carel Combrink <s25291930@tuks.co.za> wrote: > >> There are no UPDATE or DELETE, just only INSERTs into the table? > > You are correct, there are only INSERTS to the table. UPDATE and DELETE are > restricted. > >> You *should* consider an additional column, timestamp for instance, the > > trick with ctid isn't a clean solution. > > My table is defined as follow: > > =# CREATE TABLE test_select > ( > id integer, > is_active boolean, > idx serial NOT NULL, > CONSTRAINT test_select_pkey PRIMARY KEY (idx) > ); > > and the complete table looks like: > =# SELECT * FROM test_select; > id | is_active | idx > ----+-----------+----- > 5 | t | 1 > 5 | f | 2 > 6 | t | 3 > 7 | t | 4 > 8 | t | 5 > 5 | t | 6 > 8 | f | 7 > 9 | t | 8 > 10 | t | 9 > 6 | f | 10 > 10 | f | 11 > (11 rows) > > How would the query look based on the 'idx' column? > IF I replace 'ctid' with 'idx' I get: > =# select id, is_active from (select id, is_active, max(idx), row_number() > over (partition by id) from test_select group by id, is_active, idx order by > id, idx desc) foo where is_active and row_number = 1; > id | is_active > ----+----------- > 5 | t > 6 | t > 7 | t > 9 | t > 10 | t > (5 rows) > > Or if I must use a timestamp column how would I structure the query? No, you don't need a timestamp if you have your idx column. It serves the same purpose. Try this: SELECT id, is_active FROM test_select AS a INNER JOIN (SELECT max(idx) AS idx FROM test_select WHERE is_active GROUP BY id) AS b ON a.idx = b.idx; By the way, why bother returning the is_active column if it's always going to contain 'true'? Maybe you want: SELECT id FROM test_select AS a INNER JOIN (SELECT max(idx) AS idx FROM test_select GROUP BY id) AS b ON a.idx = b.idx ORDER BY id; -- Michael Wood <esiotrot@gmail.com>
Rob,
I know we could use a time stamp to do the job, but I think I read in his thread that he didn't want to redesign, so that's why I suggest using OID. But if I were him i'd put timestamp on table as you suggested.
I know we could use a time stamp to do the job, but I think I read in his thread that he didn't want to redesign, so that's why I suggest using OID. But if I were him i'd put timestamp on table as you suggested.
On Mon, Jul 26, 2010 at 12:59 PM, Michael Wood <esiotrot@gmail.com> wrote:
HiNo, you don't need a timestamp if you have your idx column. It serves
On 26 July 2010 09:55, Carel Combrink <s25291930@tuks.co.za> wrote:
>
>> There are no UPDATE or DELETE, just only INSERTs into the table?
>
> You are correct, there are only INSERTS to the table. UPDATE and DELETE are
> restricted.
>
>> You *should* consider an additional column, timestamp for instance, the
>
> trick with ctid isn't a clean solution.
>
> My table is defined as follow:
>
> =# CREATE TABLE test_select
> (
> id integer,
> is_active boolean,
> idx serial NOT NULL,
> CONSTRAINT test_select_pkey PRIMARY KEY (idx)
> );
>
> and the complete table looks like:
> =# SELECT * FROM test_select;
> id | is_active | idx
> ----+-----------+-----
> 5 | t | 1
> 5 | f | 2
> 6 | t | 3
> 7 | t | 4
> 8 | t | 5
> 5 | t | 6
> 8 | f | 7
> 9 | t | 8
> 10 | t | 9
> 6 | f | 10
> 10 | f | 11
> (11 rows)
>
> How would the query look based on the 'idx' column?
> IF I replace 'ctid' with 'idx' I get:
> =# select id, is_active from (select id, is_active, max(idx), row_number()
> over (partition by id) from test_select group by id, is_active, idx order by
> id, idx desc) foo where is_active and row_number = 1;
> id | is_active
> ----+-----------
> 5 | t
> 6 | t
> 7 | t
> 9 | t
> 10 | t
> (5 rows)
>
> Or if I must use a timestamp column how would I structure the query?
the same purpose.
Try this:
SELECT id, is_active FROM test_select AS a INNER JOIN (SELECT max(idx)
AS idx FROM test_select WHERE is_active GROUP BY id) AS b ON a.idx =
b.idx;
By the way, why bother returning the is_active column if it's always
going to contain 'true'?
Maybe you want:
SELECT id FROM test_select AS a INNER JOIN (SELECT max(idx) AS idx
FROM test_select GROUP BY id) AS b ON a.idx = b.idx ORDER BY id;
--
Michael Wood <esiotrot@gmail.com>
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
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.
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
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
Carel Combrink wrote: > I want to create a query to select only the latest ones that are active, ... >> My table is defined as follow: >> >> =# CREATE TABLE test_select >> ( >> id integer, >> is_active boolean, >> idx serial NOT NULL, >> CONSTRAINT test_select_pkey PRIMARY KEY (idx) >> ); ... >> How would the query look based on the 'idx' column? >> IF I replace 'ctid' with 'idx' I get: >> =# select id, is_active from (select id, is_active, max(idx), row_number() >> over (partition by id) from test_select group by id, is_active, idx order by >> id, idx desc) foo where is_active and row_number = 1; >> id | is_active >> ----+----------- >> 5 | t >> 6 | t >> 7 | t >> 9 | t >> 10 | t >> (5 rows) >> >> Or if I must use a timestamp column how would I structure the query? Michael Wood wrote: > No, you don't need a timestamp if you have your idx column. It serves > the same purpose. Until it wraps around. -- Lew
On 07/21/2010 12:23 AM, Lew wrote: > Carel Combrink wrote: > Someone's clock is way off. I received 6 [NOVICE} today form > 07/22/2010. I suspect the mail server is the problem. -- 73 de Donn Washburn 307 Savoy Street Email:" n5xwb@comcast.net " Sugar Land, TX 77478 LL# 1.281.242.3256 Ham Callsign N5XWB HAMs : " n5xwb@arrl.net " VoIP via Gizmo: bmw_87kbike / via Skype: n5xwbg BMW MOA #: 4146 - Ambassador " http://counter.li.org " #279316
On 28 July 2010 01:32, Donn Washburn <n5xwb@comcast.net> wrote: > On 07/21/2010 12:23 AM, Lew wrote: >> >> Carel Combrink wrote: > >> Someone's clock is way off. I received 6 [NOVICE} today form 07/22/2010. >> I suspect the mail server is the problem. It's not the clock that's off. The messages were stuck on mail.postgresql.org for a few days: [...] Received: from mx1.hub.org (mx1.hub.org [200.46.208.106]) by mx.google.com with ESMTP id v8si18828397yba.23.2010.07.27.12.56.10; Tue, 27 Jul 2010 12:56:12 -0700 (PDT) Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx1.hub.org (Postfix) with ESMTP id 9324B326833A; Tue, 27 Jul 2010 19:56:09 +0000 (UTC) Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9A52C6429B1 for <pgsql-novice-postgresql.org@mail.postgresql.org>; Wed, 21 Jul 2010 02:24:52 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 92966-08 for <pgsql-novice-postgresql.org@mail.postgresql.org>; Wed, 21 Jul 2010 05:24:44 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 2919F642997 for <pgsql-novice@postgresql.org>; Wed, 21 Jul 2010 02:24:45 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.4/8.14.4) with ESMTP id o6L5OgvD070594 for <pgsql-novice@postgresql.org>; Wed, 21 Jul 2010 02:24:43 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.4/8.14.4/Submit) id o6L5Ncpx068176 for pgsql-novice@postgresql.org; Wed, 21 Jul 2010 02:23:38 -0300 (ADT) (envelope-from news) [...] Not sure why it goes back and forth between *.hub.org and mail.postgresql.org a couple of times before being sent on its way, but appears to have something to do with anti-virus scanning. -- Michael Wood <esiotrot@gmail.com>