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

    Join Date
    Feb 2001
    Posts
    52
    Rep Power
    14

    Use of UNION invalidates SELECT results


    Related to my previous thread: http://forums.devshed.com/mysql-help-4/having-or-group-by-or-something-else-929995.html


    So I did use the UNION keyword to merge together 3 SELECT statements.

    (SELECT)

    UNION

    (SELECT)

    UNION

    (SELECT)

    Initially, the correct and expected output was generated.

    However, just the other day, the ranked-with-ties results from the first SELECT statement is incorrect.

    I'm 99.99% sure it's not a bug in the SELECT statement. Which leads me to believe that it's a bug in the engine that is returning the results.

    The entire command is:

    Code:
    (SELECT
        IF(@LastScore9 <> Score, @CurrentRank9 := @CurrentRank9 + @NextRank9, @CurrentRank9) AS Rank, Player, Games, Points, Won, Spread, PointsFor,
      IF(@LastScore9 = Score, @NextRank9 := @NextRank9 + 1, @NextRank9 := 1) as AddToGetNextRank9,
        @LastScore9 := Score AS Score
    FROM Results
    JOIN (SELECT @CurrentRank9 := 1, @LastScore9 := 0, @NextRank9 := 0) r
    WHERE Date='2012-09-11 19:00:00' AND Games=9
    ORDER BY Score DESC)
    
    UNION
    
    (SELECT
        IF(@LastScore8 <> Score, @CurrentRank8 := @CurrentRank8 + @NextRank8, @CurrentRank8) AS Rank, Player, Games, Points, Won, Spread, PointsFor,
        IF(@LastScore8 = Score, @NextRank8 := @NextRank8 + 1, @NextRank8 := 1) as AddToGetNextRank8,
        @LastScore8 := Score AS Score
    FROM Results
    JOIN (SELECT @CurrentRank8 := 1, @LastScore8 := 0, @NextRank8 := 0) r
    WHERE Date='2012-09-11 19:00:00' AND Games=8
    ORDER BY Score DESC)
    
    UNION
    
    (SELECT
        IF(@LastScore7 <> Score, @CurrentRank7 := @CurrentRank7 + @NextRank7, @CurrentRank7) AS Rank, Player, Games, Points, Won, Spread, PointsFor,
        IF(@LastScore7 = Score, @NextRank7 := @NextRank7 + 1, @NextRank7 := 1) as AddToGetNextRank7,
        @LastScore7 := Score AS Score
    FROM Results
    JOIN (SELECT @CurrentRank7 := 1, @LastScore7 := 0, @NextRank7 := 0) r
    WHERE Date='2012-09-11 19:00:00' AND Games=7
    ORDER BY Score DESC)
    The output is here: http://www.halscrib.com/index.php/web-club/results?Tournament=2012-09-11+19%3A00%3A00

    You can see that for the case of the people playing 9 games, there is SHEPHERD and FRANWARD. FRANWARD has 6 points, so she should be ranked higher than SHEPHERD. This is evident by her Score being higher.

    But it ranks SHEPHERD ahead of FRANWARD.

    If I run the first SELECT statement in phpMyAdmin by itself, it returns the correct ranking information.

    Any ideas?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    i gotta be honest wif you... TL;DR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    52
    Rep Power
    14
    Originally Posted by r937
    i gotta be honest wif you... TL;DR
    Cool story bro.

IMN logo majestic logo threadwatch logo seochat tools logo