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

restricting similar rows

From: Ciprian Popovici <ciprian(dot)popovici(at)integrare(dot)ro>
To: pgsql-novice(at)postgresql(dot)org
Subject: restricting similar rows
Date: 2002-09-19 09:01:46
Message-ID: 536399267.20020919120146@integrare.ro (view raw or flat)
Thread:
Lists: pgsql-novice
Hello, Postgres newbie here. I have the following table:

id code version active process
 1 PAP1       1      0       0
 2 PAP1       1      1       5
 3 PAP2       1      0       0
 4 PAP2       2      1       8
 5 PAP1       1      1      17

It means that: I have objects identified by 'code'. They have
versions. An object can occur more than one time, and it can either be
inactive ('active'=0) or activated ('active'=1), in which case it gets
various 'process' values.

I need a query that will return objects with a certain version, and if
there is more than one copy same object I need either an active copy
with a particular 'process' value (preferred) or the inactive copy.

So, given version=1 and process=17, I would get rows 3 and 5. #3 is
the only PAP2 with version=1, and #5 is the PAP1 that is active and
has process=17. If #5 wouldn't exist I would get #1.

Can this be done in Postgres? I tried this in MySQL:
select distinct code,max(version),max(active) from objects
where version<=1 and (process=0 or process=17) group by code;

Problem is I only get those 3 fields back (code,version,active) and I
need all of them. Plus I don't know how to properly translate this
query into Postgres.

-- Ciprian Popovici <ciprian(dot)popovici(at)integrare(dot)ro>


Responses

pgsql-novice by date

Next:From: Joel RodriguesDate: 2002-09-19 09:43:39
Subject: Re: 7.2.2 java configure problem on Mac OS X 10.1.5
Previous:From: WimDate: 2002-09-19 05:57:31
Subject: Re: [GENERAL] Still big problems with pg_dump!

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