CODEWITHSUNDEEP
sqlsql-serverdatabase
Grakon2702
Grakon2702

Reputation: 23

Same query selects different data with row_number()

Let´s say I have two tables. The first table represents Accounts and the second table represents Characters. Tables are connected with "AccountId" column. According to o game, characters are connected to accounts and every account can contain 4 characters.

In my website I have two pages. The first page is called "Characterlist.php" and generates all characters in game with one specific Authority (Column in Accounts table) which would be equal to 0.

I'm using this query:

$query = "WITH 
        Acc AS 
          (SELECT AccountId, Authority 
           FROM Account 
           WHERE Authority = '0'),
        Char AS 
          (SELECT Character.* 
          FROM Character, Acc 
          WHERE Character.AccountId = Acc.AccountId),
        Res AS 
         (SELECT *, 
         ROW_NUMBER() OVER 
        (ORDER BY Reput DESC) AS row_number 
         FROM Char)
    SELECT Res.Class, 
           Res.Name, 
           Res.Level, 
           Res.HeroLevel, 
           Res.Reput, 
           Res.row_number 
      FROM Res 
      WHERE Res.row_number >= '$charlistpagemin' 
      AND Res.row_number <= '$charlistpagemax'";

$charlistpagemin and $vharlistpagemax are php variables that I'm using to divide whole character list to several pages.

When I search characters of my testing account, their position according to their Reputation generated and ordered by row_number() is alright.

Then I have the second page userpanel.php which is shown only to signed users where they can see their game characters in another list.

I'm using almost the same query, just with different rules in the end.

$query = 
"WITH 
 Acc AS 
    (SELECT AccountId, 
     Authority 
     FROM Account 
     WHERE Authority = '0'),
  Char AS 
     (SELECT Character.* 
      FROM Character, Acc 
      WHERE Character.AccountId = Acc.AccountId),
  Res AS 
     (SELECT *, 
      ROW_NUMBER() 
      OVER (ORDER BY Reput DESC) AS row_number FROM Char)
SELECT Res.AccountId, 
       Res.Class, 
       Res.Name, 
       Res.Level, 
       Res.HeroLevel, 
       Res.Reput, 
       Res.row_number 
  FROM Res 
  WHERE Res.AccountId = '" . $_SESSION["accountid"] . "'";

And there is the problem. Their position according to Reputation is different (bad) than in characterlist.php. Where is the problem?

Edit: The table "Account" looks like:

AccountId | Authority | ... |

The table "Character" looks like:

AccountId | Class | Name | Level | HeroLevel | Reput | ... |

My testing account has 

AccountId: xxx | Authority: 0 | ... |

And has Characters

AccountId: XXX | Class: Dobrodruh | Name: Kryploij1 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |
AccountId: XXX | Class: Dobrodruh | Name: Kryploid2 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |
AccountId: XXX | Class: Dobrodruh | Name: Kryploij3 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |
AccountId: XXX | Class: Dobrodruh | Name: Grakonecek<3 | Level: 15 | Herolevel: 0 | Reput: 0 | ... |

The expected result that is shown in characterlist.php is 

Position: 139 | Class: Dobrodruh | Name: Kryploij1 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 140 | Class: Dobrodruh | Name: Kryploid2 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 141 | Class: Dobrodruh | Name: Kryploij3 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 142 | Class: Dobrodruh | Name: Grakonecek<3 | Level: 15 | Herolevel: 0 | Reput: 0 |

But the bad result in userpanel.php is:

Position: 110 | Class: Dobrodruh | Name: Kryploij1 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 111 | Class: Dobrodruh | Name: Kryploid2 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 112 | Class: Dobrodruh | Name: Kryploij3 | Level: 15 | Herolevel: 0 | Reput: 0 |
Position: 113 | Class: Dobrodruh | Name: Grakonecek<3 | Level: 15 | Herolevel: 0 | Reput: 0 |

Simply, my problem is in changed positions in userpanel. True positions are shown in characterlist. To calculate position according to ordering data in tables by Reputation I'm using row_number() function as is shown in my queries.

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your data has ties for reput and your row_number() expression is:

ROW_NUMBER() OVER (ORDER BY Reput DESC) AS row_number

Ordering in SQL is unstable. That means that rows with ties are in an arbitrary and indeterminate order -- and this order might change from one execution to the next.

Why is sorting unstable? Easy. SQL tables represent unordered sets, so there is no "underlying" ordering to define what happens in the case of ties.

The simple solution is to add another key. In your case, I think Name might be sufficient:

ROW_NUMBER() OVER (ORDER BY Reput DESC, Name) AS row_number

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29629

This is really hard to answer without sample data and inputs, but I think it is because the two queries do different things.

The first query paginates through the results, and the pagination imposes the ordering - presumably, $charlistpagemin and $charlistpagemax are 0 and 10 when you first load the page, so you get the first ten results.

The second query has not ordering - it's just a list of results, in undefined order. If you add

order by Res.row_number desc

at the end of the query, it should work.

Upvotes: 0

Related Questions