Re: union query returning duplicates

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