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>