union query returning duplicates - Mailing list pgsql-general

I am using 8.0 beta 1 on an RH 8 Linux server.

I have a union query that I am converting from access (where it
worked) and it is returning duplicates. The only difference between
the two rows is the Row field, which is returned automatically.

and an example of a row that it has returned duplicate. I have
verified that the row only shows up 1 time in each select statement
when run individually.

Here is a sample of the duplicates it returned (one was row 2 and the
other row 3, but that didn't seem to come with a copy and paste from
pgadmin):
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97;3;6.6
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97;3;6.6


Below is the query:
 SELECT a.assemblyid, a.assemblyname, b.fixedprice,
        CASE
            WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
            ELSE sum(calculatedprice)
        END AS calcprice, b.supplierid, a.productid, COALESCE(b.fixedprice,
        CASE
            WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
            ELSE sum(calculatedprice)
        END) AS activeprice
   FROM assemblies a
   JOIN qry_assemblyfixedprices b ON a.assemblyid = b.assemblyid
   LEFT JOIN qry_assemblycalcprices c ON c.supplierid = b.supplierid AND b.assemblyid = c.assemblyid
  WHERE b.supplierid =97
  GROUP BY a.assemblyid, a.assemblyname, b.fixedprice, b.supplierid, totalcount, a.productid
order by assemblyid
UNION
 SELECT a.assemblyid, a.assemblyname, c.fixedprice,
        CASE
            WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
            ELSE sum(calculatedprice)
        END AS calcprice, b.supplierid, a.productid, COALESCE(c.fixedprice,
        CASE
            WHEN sum(packagecount) <> totalcount::numeric THEN NULL::double precision
            ELSE sum(calculatedprice)
        END) AS activeprice
   FROM assemblies a
   JOIN qry_assemblycalcprices b ON a.assemblyid = b.assemblyid
   LEFT JOIN qry_assemblyfixedprices c ON c.supplierid = b.supplierid AND c.assemblyid = b.assemblyid
  WHERE b.supplierid =97
  GROUP BY a.assemblyid, a.assemblyname, c.fixedprice, b.supplierid, totalcount, a.productid
        order by assemblyid


Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax