473,839 Members | 1,375 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END AS calcprice, b.supplierid, a.productid, COALESCE(b.fixe dprice,
CASE
WHEN sum(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END) AS activeprice
FROM assemblies a
JOIN qry_assemblyfix edprices b ON a.assemblyid = b.assemblyid
LEFT JOIN qry_assemblycal cprices 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(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END AS calcprice, b.supplierid, a.productid, COALESCE(c.fixe dprice,
CASE
WHEN sum(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END) AS activeprice
FROM assemblies a
JOIN qry_assemblycal cprices b ON a.assemblyid = b.assemblyid
LEFT JOIN qry_assemblyfix edprices 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 4830
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*******@postg resql.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
3579
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 from letdata UNION SELECT as ID FROM MEMODATA; I get an ODBC error. The same query runs when the backend files are MDB files and it runs with MYSQL if I only combine 2 tables.
4
1688
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 there's another (better) solution than UNION operator in this case. TIA. -- DDL CREATE TABLE #TMP (col varchar(10));
3
4615
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: tblItems ItemID ItemLabel
5
3301
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 many fields including picture and memo fields. The main user-selectable field is 'NameA'. There is also a crossreference table (let's call it 'tblB') which provides a secondary method of accessing records in 'tblA'. The main fields in 'tblB'...
2
4347
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 Count 1234 1 2468 1 1234 1 2468 1
4
7828
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 add INSERT INTO, then it doesn't work:
6
4600
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 bn 45 abc dir I now want to get the data from this table in this format:
5
2283
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 the query produced and opened it from a MS Query it started giving strange results. The first query when run alone returns 22 records, some of which have identical values in all fields. This is 100% correct. The second query returns nothing....
5
2515
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 table) that has all the dependents of the employees. I have also have an alternate ID table in case an employee has more than 1 id. I have to export all the data into a fixed-text-length file with very combination of the above: meaning all lives,...
0
9855
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9697
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10295
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7829
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7018
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5682
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5867
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.