Recognizing duplicates while iterating through query results

Question

This is a follow-up to a question I asked previously.

I can calculate the rank (including the logic for ties) just fine; the issue is detecting future duplicates when I come across the first instance of a duplicate rank.

Here is the SQL query to get the result set:

SELECT
    s1.team_id,
    sum(s1.score>s2.score) wins
FROM scoreboard s1
    LEFT JOIN scoreboard s2
        ON s1.year=s2.year
        AND s1.week=s2.week
        AND s1.playoffs=s2.playoffs
        AND s1.game_id=s2.game_id
        AND s1.location<>s2.location
GROUP BY s1.team_id
ORDER BY wins DESC;

Here is the sample SQL result set which I'll loop through in PHP:

team_id   wins
--------------
10        52
2         48
5         46
11        46
3         42
9         39
...

Here is my PHP code for the display, which needs to append "T-" to all tied ranks:

$i = 0;
while($row = mysql_fetch_assoc($r)) { //iterate thru ordered (desc) SQL results
    ++$i;
    ($row['wins'] == $prev_val)
        ? $rnk = 'T-' . $rnk    //same as previous score, indicate tie
        : $rnk = $i;            //not same as previous score
    $rnk = str_replace('T-T-','T-',$rnk); //eliminate duplicative tie indicator
    if ($row['team_id'] == $team_id) { //current team in resultset matches team in question, set team's rank
        $arr_ranks['tp']['cat'] = 'Total Wins';
        $arr_ranks['tp']['actual'] = number_format($row['wins'],1);
        $arr_ranks['tp']['league_rank'] = $rnk;
        $arr_ranks['tp']['div_rank'] = $div_rnk;
    }
    else if ($i == 1) { //current team is category leader (rank=1) and is not team in question, set current team as leader
        $arr_ranks['tp']['leader'] = "<a href='index.php?view=franchise&team_id=" . $row['team_id'] . "'>" . get_team_name($row['team_id']) . '</a> (' . number_format($row['wins']) . ')';
    }
    $prev_val = $row['wins']; //set current score as previous score for next iteration of loop
}

The "tie" logic above will capture team #4 as having tied with team #3, but not vice versa.

In other words, for team #3, $rnk = 3, while for team #4, $rnk = T-3. (Both should be "T-3".)

So the question becomes: how do I "look ahead" while iterating through the results to find out if the current score is a tie/duplicate of scores further down the list, so I can treat it as a tie along with the subsequent dupes?

@Airzooka gave me a potential solution, but I'm curious to know if there's a more efficient way to do it (possibly at the SQL level even).

Thanks.

why not reading once and for all the entire points list in a loop, then checking duplicates in a next loop using array_search perhaps? — , Jun 12 '12 at 17:11

Brian Warshaw · Answer 1 · 2012-06-12T19:57:16.133

1

In pseudo-code:

loop through rows as row1
    loop through rows as row2
        if row1 ain't row2 and row1.points == row2.points, append T

Update:

Ok, how about this, since you're ordering your result set by wins, anyhow: try storing information about each row in a temporary array or variables, like $previousTeamWins, $previousTeamName, etc. Then you can compare the current and the previous and assign the T based on that. So you're effectively delaying the assignment until the following iteration (or until the exit of the loop in the case of the final row). One trip through the row set, should get the job done.

edited Jun 12 '12 at 19:57

answered Jun 12 '12 at 19:39

Brian Warshaw

22,657
9
53
72

See my comments above. I have to calculate the values via a query; they aren't pre-populated in a readily available table. – hannebaumsaway Jun 12 '12 at 19:42
That's fine--this pseudo-code is telling you what to do when you have rows of information. I'm assuming you already understand looping in PHP, so it should be trivial to implement. – Brian Warshaw Jun 12 '12 at 19:43
I think the [solution provided by Airzooka](stackoverflow.com/questions/10855462/detect-future-duplicate-values-while-iterating-through-mysql-results-in-php) would be more efficient than looping through the results twice. I'm curious to know if there is an even more efficient method than that one, either straight through SQL or not. – hannebaumsaway Jun 12 '12 at 19:46
His is slightly more efficient, yes, but only when there are ties--if there are no ties, you still effectively loop through the results twice. – Brian Warshaw Jun 12 '12 at 19:50
So would you say there isn't an effective way to detect ties ahead of time at the SQL level, then? – hannebaumsaway Jun 12 '12 at 19:54
Edited my answer--hopefully a little better. – Brian Warshaw Jun 12 '12 at 19:57
Could be messy at the SQL level because the wins column is "virtual". – Brian Warshaw Jun 12 '12 at 19:59
Thanks for the edit, but not sure I understand. Are you saying to store my eventual output rows in an array first, so I can retroactively append the "T-" to the previous row if the current row is a duplicate? – hannebaumsaway Jun 12 '12 at 20:04
I'm saying, either in an array or in a few temporary variables, buffer each row and don't write it to `$arr_ranks` until the following iteration (or end of loop). That way you're always able to compare with the following item before you write. And you won't have to use two loops. – Brian Warshaw Jun 12 '12 at 20:08
Additionally, you can add another index to the 'tp' array, and call it "tie". You can set its value to true or false. That way you don't have to strip out the 'T' for comparing with the next row (if the current row is a tie with the previous row), and you can add the 'T' when you output to the browser based on the value of the 'tie' index. – Brian Warshaw Jun 12 '12 at 20:12

score 0 · Answer 2 · answered Jun 12 '12 at 17:50

0

What about this?

SELECT
 t1.*,
 EXISTS (
  SELECT *
  FROM `teams` as t2
  WHERE t2.pts = t1.pts AND t1.id != t2.id
 ) as `tied`
 FROM `teams` as t1
...

answered Jun 12 '12 at 17:50

Matteo B.

3,906
2
29
43

Thanks, but that only works if the results are stored in a table to begin with, which mine aren't. I added my query above in an edit so you can see that I actually have to calculate the values which I then check to see are dupes. – hannebaumsaway Jun 12 '12 at 19:33
Your results aren't in a table? Perhaps you should remove the enormous header that reads: *Here is the SQL query to get the result set:* – Brian Warshaw Jun 12 '12 at 19:35
@BrianWarshaw The value which I'm checking for duplication ("wins") isn't in a table. As you can see, I have to calculate it on the fly by comparing scores. – hannebaumsaway Jun 12 '12 at 19:42

Query Master · Answer 3 · 2012-06-12T18:26:58.157

0

Try this i hope its work for you

SELECT t1.`id`,t1.`pts`,t1.`team_id`,
IF(t1.`pts` = t2.`pts` AND t1.`id` != t2.`id` ,1,0) AS `tied`
FROM `teams` t1
LEFT JOIN `teams` t2 on t2.`pts` = t1.`pts` AND t2.`id` != t1.`id`
GROUP bY t1.`id`

edited Jun 12 '12 at 18:26

answered Jun 12 '12 at 18:18

Query Master

6,989
5
35
58

Thanks, but I believe the comment I made to @Matmarbon above applies here as well... – hannebaumsaway Jun 12 '12 at 19:33

score 0 · Answer 4 · answered Jun 12 '12 at 20:27

Oi, still looking at your code. So at the end of the day, you only want to output one or two teams, right? One if the leader is the team in question, and two otherwise. If that's the case, try something like this (with the warning that it isn't tested):

$i = 0;
while($row = mysql_fetch_assoc($r)) { //iterate thru ordered (desc) SQL results 
    ++$i; 
    ($row['wins'] == $prev_wins) 
        ? $rnk = $prev_rnk    //same as previous score, indicate tie 
        : $rnk = $i;            //not same as previous score 
    $rnk = str_replace('T-T-','T-',$rnk); //eliminate duplicative tie indicator 

    if ($prev_team_id == $team_id) {
        $arr_ranks['tp']['cat'] = 'Total Wins'; 
        $arr_ranks['tp']['actual'] = number_format($prev_wins,1); 
        $arr_ranks['tp']['league_rank'] = $prev_rnk; 
        $arr_ranks['tp']['div_rank'] = $div_rnk;

        if ($row['wins'] == $prev_wins)
        {
            $arr_ranks['tp']['tie'] = true;
        }
        else
        {
            $arr_ranks['tp']['tie'] = false;
        }

    break;
    }
    else if ($i == 1) { //current team is category leader (rank=1) and is not team in question, set current team as leader 
        $arr_ranks['tp']['leader'] = "<a href='index.php?view=franchise&team_id=" . $row['team_id'] . "'>" . get_team_name($row['team_id']) . '</a> (' . number_format($row['wins']) . ')'; 
    }

    $prev_wins = $row['wins'];
    $prev_team_id = $row['team_id'];
    $prev_rnk = $rnk;
}

if ($prev_team_id == $team_id) {
    $arr_ranks['tp']['cat'] = 'Total Wins'; 
    $arr_ranks['tp']['actual'] = number_format($prev_wins,1); 
    $arr_ranks['tp']['league_rank'] = $prev_rnk; 
    $arr_ranks['tp']['div_rank'] = $div_rnk;

    if ($row['wins'] == $prev_wins)
    {
        $arr_ranks['tp']['tie'] = true;
    }
    else
    {
        $arr_ranks['tp']['tie'] = false;
    }

}

Actually, the output is for a single team only per page. The output is the Category, Actual Value, Rank (hopefully including "T-" if the team in question is tied with another), and League Leader. — hannebaumsaway, Jun 12 '12 at 20:39

score 0 · Answer 5 · answered Jun 13 '12 at 16:52

I think I found a solution via SQL! May not be elegant (I can clean it up later), but here goes...

The query:

SELECT a.team_id, a.wins, count(*) instances
FROM
    (SELECT
        s1.team_id,
        sum(s1.score>s2.score) wins
    FROM scoreboard s1
        LEFT JOIN scoreboard s2
            ON s1.year=s2.year
            AND s1.week=s2.week
            AND s1.playoffs=s2.playoffs
            AND s1.game_id=s2.game_id
            AND s1.location<>s2.location
    GROUP BY s1.team_id) AS a
    LEFT JOIN
        (SELECT
            sum(s1.score>s2.score) wins
        FROM scoreboard s1
            LEFT JOIN scoreboard s2
                ON s1.year=s2.year
                AND s1.week=s2.week
                AND s1.playoffs=s2.playoffs
                AND s1.game_id=s2.game_id
                AND s1.location<>s2.location
        GROUP BY s1.team_id) AS b
            ON a.wins = b.wins
GROUP BY a.team_id, b.wins
ORDER BY a.wins DESC;

This gives the output...

=================================
|team_id   | wins    |instances |
=================================
|10        | 44      |1         |
|2         | 42      |3         | //tie
|9         | 42      |3         | //tie
|5         | 42      |3         | //tie
|3         | 41      |1         |
|11        | 40      |1         |
|...       |         |          |
=================================

Then, in PHP, I'll be able to detect all ties by checking when $row['instances'] > 1.

Thanks all for bearing with me through this unnaturally cumbersome issue!

Recognizing duplicates while iterating through query results

Here is the SQL query to get the result set:

Here is the sample SQL result set which I'll loop through in PHP:

Here is my PHP code for the display, which needs to append "T-" to all tied ranks:

5 Answers5

Linked