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? — user1299518
– user1299518, Commented Jun 12, 2012 at 17:11

Brian Warshaw · Accepted Answer · 2012-06-12 19:57:16Z

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, 2012 at 19:57

answered Jun 12, 2012 at 19:39

Brian Warshaw

23k9 gold badges55 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

hannebaumsaway Over a year ago

See my comments above. I have to calculate the values via a query; they aren't pre-populated in a readily available table.

Brian Warshaw Over a year ago

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.

hannebaumsaway Over a year ago

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.

Brian Warshaw Over a year ago

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.

hannebaumsaway Over a year ago

So would you say there isn't an effective way to detect ties ahead of time at the SQL level, then?

|

Matteo B. · Accepted Answer · 2012-06-12 17:50:42Z

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, 2012 at 17:50

Matteo B.

4,0942 gold badges35 silver badges43 bronze badges

3 Comments

hannebaumsaway Over a year ago

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.

Brian Warshaw Over a year ago

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:

hannebaumsaway Over a year ago

@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.

Query Master · Accepted Answer · 2012-06-12 18:26:58Z

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, 2012 at 18:26

answered Jun 12, 2012 at 18:18

Query Master

7,1226 gold badges37 silver badges58 bronze badges

1 Comment

hannebaumsaway Over a year ago

Thanks, but I believe the comment I made to @Matmarbon above applies here as well...

Brian Warshaw · Accepted Answer · 2012-06-12 20:27:18Z

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 · Accepted Answer · 2012-06-13 16:52:57Z

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!

Collectives™ on Stack Overflow

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 Answers 5

10 Comments

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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 Answers 5

10 Comments

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related