Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 055299F9C35 for ; Fri, 15 Dec 2006 17:15:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 06384-07 for ; Fri, 15 Dec 2006 17:15:19 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id A88409FA252 for ; Fri, 15 Dec 2006 17:15:22 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c24so306090ana for ; Fri, 15 Dec 2006 13:15:21 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=AJk+be1X2ErxmQNHVKS4Hmqo+MscDUKiACtlDUZPVclkApaS6JnWxyn0UcTqbMKH7FVyoLr6CblYJFplK/P6hdJidmY9ysnLlauR9yN3NnRHdr5yTBCFvyf/J+nqu0vXf3sJ9rYJnfn59uppVcLDzSmuFCa/1nGrpq8hd6humzc= Received: by 10.78.203.13 with SMTP id a13mr498680hug.1166217312180; Fri, 15 Dec 2006 13:15:12 -0800 (PST) Received: by 10.78.131.12 with HTTP; Fri, 15 Dec 2006 13:15:12 -0800 (PST) Message-ID: Date: Fri, 15 Dec 2006 15:15:12 -0600 From: "Aaron Bono" To: "Postgres SQL language list" Subject: Unions and Grouping MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_164159_2446573.1166217312125" X-Google-Sender-Auth: adaa5f5b7fd59b0d X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/121 X-Sequence-Number: 27077 ------=_Part_164159_2446573.1166217312125 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a question about the SQL Specifications in regards to Unions... I recently put together a query that involved unions similar to the following: SELECT 'Query 1' as id, my_value FROM my_view UNION SELECT 'Query 2' as id, my_value FROM my_other_view ; The first query in the union gave me 39 records and the second gave me 34 records. I was expecting the union to give me 39 + 34 = 73 records. When I ran this against DB2, I got 35 records (not sure about PostgreSQL - will have to try it when I get home). What I found was when I did a group by my_value on each query I got two values that then added to 35. The reason was, my_value was duplicated in my_view and in my_other_view. What the Union appeared to be doing was to gather the data and then do a group by on the complete results. I expected it to only eliminate duplicates BETWEEN the two queries, not WITHIN the queries. My question, what do the SQL Specifications say should happen on a Union? Is it supposed to eliminate duplicates even WITHIN the individual queries that are being unioned? Thanks! -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_164159_2446573.1166217312125 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a question about the SQL Specifications in regards to Unions...

I recently put together a query that involved unions similar to the following:

SELECT
    'Query 1' as id,
    my_value
FROM my_view
UNION
SELECT
    'Query 2' as id,
    my_value
FROM my_other_view
;

The first query in the union gave me 39 records and the second gave me 34 records.  I was expecting the union to give me 39 + 34 = 73 records.

When I ran this against DB2, I got 35 records (not sure about PostgreSQL - will have to try it when I get home).  What I found was when I did a group by my_value on each query I got two values that then added to 35.  The reason was, my_value was duplicated in my_view and in my_other_view.  What the Union appeared to be doing was to gather the data and then do a group by on the complete results.  I expected it to only eliminate duplicates BETWEEN the two queries, not WITHIN the queries.

My question, what do the SQL Specifications say should happen on a Union?  Is it supposed to eliminate duplicates even WITHIN the individual queries that are being unioned?

Thanks!

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_164159_2446573.1166217312125--