need help with distinct results

[Yun Chie <fl...@gmail.com>]

Hi all,

I have the following statement (very roughly):

SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers, Sales
...
INTERSECT 
SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers, Sales
...

I just need to know what's the right syntax to retrieve only the distinct
results, as,  the above statement retrieve also duplicates, in other words,
what is in derby the right syntax for the following statement:

SELECT Customer.Customer_UID, Sales.Sales_UID (
(SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers,
Sales ...)
INTERSECT 
(SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers,
Sales ...)
)

thanks for your help.
-- 
View this message in context: http://www.nabble.com/need-help-with-distinct-results-tp18157186p18157186.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: need help with distinct results

[Rick Hillegas <Ri...@Sun.COM>]

I am not sure what is going on with your query. It would be helpful if 
you could post the full query and the results you are seeing.

I have run some simple experiments with the INTERSECT operator and I 
have seen the following behavior:

1) The default behavior of the INTERSECT operator is to remove 
duplicates. That is, INTERSECT is equivalent to INTERSECT DISTINCT. This 
conforms to the the ANSI 2003 spec, volume 2, section 7.13 (query 
expression), syntax rule 6.

2) INTERSECT ALL returns duplicates, which looks correct to me.

Here's the simple experiment I ran:

ij> create table t( a int );
0 rows inserted/updated/deleted
ij> insert into t( a ) values
( 1 ), ( 2 ), ( 3 ), ( 4 );
4 rows inserted/updated/deleted
ij> insert into t select * from t;
4 rows inserted/updated/deleted
ij> select a from t
intersect
select a from t
;
A         
-----------
1         
2         
3         
4         

4 rows selected
ij> select a from t
intersect all
select a from t
;
A         
-----------
1         
1         
2         
2         
3         
3         
4         
4         

8 rows selected
ij> select a from t
intersect distinct
select a from t
;
A         
-----------
1         
2         
3         
4         

4 rows selected

Hope this helps,
-Rick

Yun Chie wrote:
> Hi all,
>
> I have the following statement (very roughly):
>
> SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers, Sales
> ...
> INTERSECT 
> SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers, Sales
> ...
>
> I just need to know what's the right syntax to retrieve only the distinct
> results, as,  the above statement retrieve also duplicates, in other words,
> what is in derby the right syntax for the following statement:
>
> SELECT Customer.Customer_UID, Sales.Sales_UID (
> (SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers,
> Sales ...)
> INTERSECT 
> (SELECT DISTINCT Customer.Customer_UID, Sales.Sales_UID FROM Customers,
> Sales ...)
> )
>
> thanks for your help.
>