#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    2
    Rep Power
    0

    SQL UNION problem


    I am getting a different (better) result when executing the second half of the UNION separately. Any ideas??? Each row of the table contains both buy and sell transaction data so there should be a sell value for every buy value in the UNION output.

    select t.account, t.ticker, bshares, bdate,
    (-bshares*bprice) amount
    from tran t
    join quote q on t.ticker = q.ticker
    where sdate is null
    and account = 'CORP'
    UNION
    select t.account,
    t.ticker,
    bshares,
    '2013/12/14',
    (bshares*q.quote)
    from tran t
    join quote q on t.ticker = q.ticker
    where sdate is null
    and account = 'CORP'
    order by account, ticker

    UNION RESULT
    "CORP";"GTLS";50;"2013-08-01";-5813.500
    "CORP";"GTLS";50;"2013-12-14";4522.500
    "CORP";"TSLA";25;"2013-05-13";-2154.750
    "CORP";"TSLA";25;"2013-07-17";-2915.750
    "CORP";"TSLA";25;"2013-12-14";3735.000

    INDEPENDENT (2nd half) RESULT (desired result)
    "CORP";"GTLS";50;"2013/12/14";4522.500
    "CORP";"TSLA";25;"2013/12/14";3735.000
    "CORP";"TSLA";25;"2013/12/14";3735.000
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    "UNION" will filter duplicates, UNION ALL will simply append the results from both queries.

    Also, the ORDER BY at the end may be applied to the entire UNION result, not just the second query, which wil cause a different queryplan.

    Speaking of the qyery paln, what does EXPLAIN say about the whole query vs just the second half?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    2
    Rep Power
    0

    SQL UNION problem


    Originally Posted by Vinny42
    "UNION" will filter duplicates, UNION ALL will simply append the results from both queries.

    Also, the ORDER BY at the end may be applied to the entire UNION result, not just the second query, which wil cause a different queryplan.

    Speaking of the qyery paln, what does EXPLAIN say about the whole query vs just the second half?
    Vinny - Thanks. The 2nd half explain is the same - only the append is missing. I have used MS SS much more than PG 9.3 but wasn't aware of "ALL". I may have avoided errors because of primary key inclusion. Joe Rimback
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by Design9
    I have used MS SS much more than PG 9.3 but wasn't aware of "ALL".
    SQL Server works exactly the same way regarding UNION and UNION ALL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Originally Posted by Design9
    Vinny - Thanks. The 2nd half explain is the same - only the append is missing. I have used MS SS much more than PG 9.3 but wasn't aware of "ALL". I may have avoided errors because of primary key inclusion. Joe Rimback
    Isn't there another step missing, the one that removes the duplicates (that may be included in the append though, I've never really looked for that in an EXPLAIN output )

    Anyway, does adding ALL resolve the speed problem? Because then we can look for a different way of removing the duplicates.

IMN logo majestic logo threadwatch logo seochat tools logo