Обсуждение: 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>