Обсуждение: restricting similar rows

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.popovici@integrare.ro>

Thursday, September 19, 2002, 12:01:46 PM, Ciprian Popovici <ciprian.popovici@integrare.ro> wrote:
> need all of them. Plus I don't know how to properly translate this
> query into Postgres.

Figured it out, eventually. Here's the Postgres query:

select distinct on (code) \
  id,code,active from objects \
  where version<=1 and (process=17 or process=0) \
  group by id,code,active,version \
  order by code asc,active desc,version desc

I supply version and process values and I get whatever I need back
(id,code,active). Kudos to Postgres for the 'distinct on' clause.

-- Ciprian Popovici <ciprian.popovici@integrare.ro>