Re: Union query dropping "duplicate" rows

From: Ray Higdon (sqlhigdon_at_nospam.yahoo.com)
Date: 02/28/04 

Date: Sat, 28 Feb 2004 13:36:53 -0500

Try UNION ALL, UNION ALL does not remove dupes 

-- 
Ray Higdon MCSE, MCDBA, CCNA
---
"Don Jellie" <anonymous@discussions.microsoft.com> wrote in message
news:9A3F81D7-AAB4-4358-AF32-1C43DEEEF7A1@microsoft.com...
> Hi all,
>
> I have 3 questions about the same issue.  I a have union query across 2
almost identical inventory tables (1 current year & 1 historical) that is
dropping rows it thinks are duplicates.  They're not dups, though.  They
just look like it.  The real thing is massive, but a simplified example
follows.
>
> SQL:
> SELECT
> CLASS, ITEM, SUM(COUNT)
> FROM
> CURRENT_TBL
> GROUP BY
> CLASS, ITEM
> UNION
> SELECT
> CLASS, ITEM, SUM(COUNT)
> FROM
> HISTORY_TBL
> GROUP BY
> CLASS, ITEM
> Orde by 1, 2
>
> Data:
> CLASS ITEM COUNT
> Fruit Apples 7
> Fruit Apples 100
> Veggies Peas 50
> Veggies Peas 50 (This row gets dropped!)
>
> Questions:
> 1) Why is this happening and should I have expected it?
> 2) Is there syntax that will pull both the current and historical veggie
rows even though they're identical?
> 3) I tried solving this by adding a unique column to each union (e.g.
SELECT 'CUR' AS CUR, CLASS, ITEM, SUM(COUNT)) but I get an error on GROUP BY
CUR, CLASS, ITEM.  Can I not do that or is my syntax just bad?
>
> Thanks a lot in advance,
> DJ
>

Relevant Pages

  • Re: Union query dropping "duplicate" rows
    ... Try UNION ALL, UNION ALL does not remove dupes ... Ray Higdon MCSE, MCDBA, CCNA ... dropping rows it thinks are duplicates. ... SELECT 'CUR' AS CUR, CLASS, ITEM, SUM) but I get an error on GROUP BY ...
    (microsoft.public.sqlserver.mseq)
  • Re: Not Common both Tables
    ... For exemple, I'm sure that the Full Outer Join is not available under ... I'm pretty sure that the Exitsstatement and the UNION ... For the syntax error, it's probably a missing AND in the first subquery of ... You can also use an Outer Join and select all the records which will have ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Management Studio & Views containing unions
    ... display the View, which I guess is OK, the old tool couldn't do that ... the syntax verification doesn't work either.. ... "Incorrect Syntax near UNION" error each time. ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)
  • Re: retrieving top 1 of ordered union
    ... Syntax is not your problem. ... You think that SQL's UNION is the same ... 1NF and make it look like a tape file. ... Please post DDL, so that people do not have to guess what the keys, ...
    (microsoft.public.sqlserver.programming)
  • Management Studio & Views containing unions
    ... editing Views that contain a UNION statement. ... For starters, it can't display ... I get an "Incorrect Syntax ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)