CODEWITHSUNDEEP
mysql
the_big_blackbox
the_big_blackbox

Reputation: 1196

1 select statement 2 different results

I have two versions of sql set up - one is on a remote machine(mariadb) and the other is on a local machine(wamp)mysql. I have a query that runs correctly on the local version however I receive a different result on the remote version. The storage engine type for each table is the same in both db's. The storage engine for the db's is however different - one is innodb and the other myisam. 

SELECT @v:=@v+1 as rank, t.*
FROM (
      SELECT version.id, 
             CAST((st.up-st.down) as SIGNED) votes, 
             meta.title 
        FROM version 
        JOIN st ON version.id=st.id 
        JOIN meta ON version.id=meta.id 
       ORDER BY votes DESC
    ) t

Local version - correct result 

rank | id | votes | title 
 1     12    100    hello
 2     20     50    world
 3     5      20    helloworld

Remote version - incorrect result

rank | id | votes | title
 1     5     20     helloworld
 2     12    100    hello
 3     20    50     world

Upvotes: 0

Views: 41

Answers (3)

mpardo
mpardo

Reputation: 13

Hello if you still wanna keep the subquery try

`

SELECT @v:=@v+1 as rank, t.*
FROM (
      SELECT version.id, 
             CAST((st.up-st.down) as SIGNED) votes, 
             meta.title 
        FROM version 
        JOIN st ON version.id=st.id 
        JOIN meta ON version.id=meta.id 
    ) t
CROSS JOIN(select @v := 0) r
ORDER BY t.votes DESC

`

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Perhaps you were thinking of this...

SELECT version.id
     , CAST(st.up-st.down as SIGNED) votes
     , meta.title
     , @v:=@v+1 rank
  FROM version 
  JOIN st 
    ON version.id = st.id 
  JOIN meta ON version.id = meta.id
  JOIN (SELECT @v:=1) vars
 ORDER 
    BY votes DESC

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Data in a table is considered unordered. A subquery in the FROM clause is a (derived) table.

So your ORDER BY clause in the subquery is superfluous. MySQL is free to execute it or not.

There is nothing wrong with the results. Your assumption on what the query does is wrong.

Upvotes: 2

Related Questions