PHP + mySQL - 在计数查询后获取行的共享位置

Question

So I'm trying to display individual ranks of each seller within their panel on my marketplace site. For this specific rank, I'm counting how many followers each seller has, ordering the list in descending order, and then getting the position of each one so they know where they rank. I'm using a query that works, except when two sellers have the same number of followers, the position changes every time. This is especially bothersome when there are large number of sellers with the same number of followers.

Query (in PHP):

<?php 
    $query = "SELECT rank
        FROM (
          SELECT vendor_id, followers, @rank:=@rank+1 AS rank
          FROM (
            SELECT vendor_id, COUNT(*) AS followers
            FROM $table
            GROUP BY vendor_id
            ORDER BY followers DESC
           ) as sq,
            (SELECT @rank:=0) AS tr
          ) as q WHERE vendor_id = $vendorId";
?>

where $vendorId = the current seller's ID.

So, again, the main issue is that if there are 10 sellers, and 7 of them all have 1 follower. Each time the seller refreshes, they'll see a ranking anywhere from 4-10. I'd like it to just say 4 for all sellers who have the 1 follower.

Update: The following query has given me what I wanted, though I'd still prefer pushing the workload to PHP than keeping it all on MySQL.

<?php
    $connection->query("SET @curRank:=1, @prevRank:=0, @incRank=1;");

    $query = "SELECT rank
    FROM (
    SELECT vendor_id, followers,
      @curRank := IF(@prevRank = followers, @curRank, @incRank) AS rank,
      @incRank := @incRank + 1,
      @prevRank := followers
        FROM (
    SELECT vendor_id, COUNT(1) AS followers
    FROM $favTable
            GROUP BY vendor_id
    ORDER BY followers DESC, vendor_id DESC
        ) as sq,
        (SELECT @rank:=0) AS tr
     ) as q WHERE vendor_id = $vendorId";

    $result = $connection->fetchOne($query);

Answer 1

You are on the right path. You just need to add a couple more vars in the query. You could try the following:

<?php 
$query = "SELECT rank
    FROM (
      SELECT vendor_id, followers,
          @curRank := IF(@prevRank = followers, @curRank, @incRank) AS rank, 
          @incRank := @incRank + 1, 
          @prevRank := followers
      FROM (
        SELECT vendor_id, COUNT(*) AS followers
        FROM $table
        GROUP BY vendor_id
        ORDER BY followers DESC, vendor_id
       ) as sq,
        (SELECT @rank:=0) AS tr
      ) as q WHERE vendor_id = $vendorId";
?>

Please add a more specific sort order as mentioned by @swa66 to get consistent ordered rows. When the field used in sort has same values, mysql may return rows based on its own ordering logic.