473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
3 4787
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

Nov 23 '05 #2
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
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)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID ...
4
by: NickName | last post by:
Hello, Bear with me (not had much sleep last night), pls see following ddl, dml and comments for what is desired, I don't have a problem getting desired result(s), however, I'm wondering if...
3
by: Tom Mitchell | last post by:
All: I'm stumped on a query. How do I find duplicates in a table where one of the duplicates has values is a certain field and the other doesn't. For example, I have the following table: ...
5
by: Lyn | last post by:
This one is difficult to explain, so I will cut it down to the basics. I have a major table 'tblA' which has an autonum field 'ID-A' as primary key (of no significance to users). 'tblA' contains...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code ...
4
by: spam | last post by:
If I run the following query in Access 2002 then I get the expected result: SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp; I get the contents of both tables with no duplicates. If I...
6
by: das | last post by:
Hello all, I have a table with thousands of rows and is in this format: id col1 col2 col3 col4 --- ------ ----- ------ ------ 1 nm 78 xyz pir 2 ...
5
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed...
5
zachster17
by: zachster17 | last post by:
Hi everyone, First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.