Thread: union query returning duplicates
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
It is very weird, I just tried both a group by and distinct and both of them still return the duplicates. I also tried a very simple union which didn't return any duplicates, both of these said, it is obviously not a problem with union. I just tried the query without the case statement that does the sum and it did work. I am wondering if there might be something about double precision numbers (such as a weird roundoff error or something) that prevent it from comparing it to another number. In my example it is returning fairly simple numbers (6.6) so I don't see where it could make a mistake. The system automatically put in the ::double precision when I created the View that encases the query I sent. Maybe there is a better typecast that I should use to manually override it? Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ________________________________________________________________________________ Did you tried a select distinct? Hagen Sim Zacks wrote: >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 > > > > > >
double precision is inexact and therefore any query returning a field
of that type cannot be in a group by/distinct...
I switched it to type ::numeric(10,4) and it worked fine.
It was the system that automatically did the conversion for me, so I
will have to figure out why and keep that in mind for the next time.
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
________________________________________________________________________________
It is very weird, I just tried both a group by and distinct and both
of them still return the duplicates.
I also tried a very simple union which didn't return any duplicates,
both of these said, it is obviously not a problem with union.
I just tried the query without the case statement that does the sum
and it did work. I am wondering if there might be something about
double precision numbers (such as a weird roundoff error or something) that prevent
it from comparing it to another number.
In my example it is returning fairly simple numbers (6.6) so I don't
see where it could make a mistake.
The system automatically put in the ::double precision when I created the
View that encases the query I sent. Maybe there is a better typecast
that I should use to manually override it?
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
________________________________________________________________________________
Did you tried a select distinct?
Hagen
Sim Zacks wrote:
>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
>
>
>
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Wed, Oct 20, 2004 at 01:54:04PM +0200, Sim Zacks wrote: > It is very weird, I just tried both a group by and distinct and both > of them still return the duplicates. > > I also tried a very simple union which didn't return any duplicates, > both of these said, it is obviously not a problem with union. Not related to your underlying problem, but be aware that UNION does eliminate duplicates by design, so that could explain what you are seeing here. If you don't want it to do that, use UNION ALL instead (the same applies to INTERSECT and EXCEPT if you ever happen to use them). -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Saca el libro que tu religión considere como el indicado para encontrar la oración que traiga paz a tu alma. Luego rebootea el computador y ve si funciona" (Carlos Duclós)