What's wrong with my query? Works on sqlyog, but nowhere else.

[sKunKbad]

I have a need to do a special sorting for a bunch of leads. The person who sees the leads (l.user) is looking at leads that are for a few different people (l.team_id). When I run my query in sqlyog, it gives me the results I am looking for, where the rank is used to sort. When I run my query through phpMyAdmin, php, or the MySQL command line, all of the ranks are NULL, so nothing gets sorted. All of these attempts are on the same database, on the same computer (Windows 7). Any ideas or help is appreciated.

SELECT 
  x.lead_id,
  x.rank,
  x.team_id,
  x.status 
FROM
  (SELECT 
    l.lead_id,
    l.team_id,
    l.status,
    CASE
      WHEN @ps != l.team_id 
      THEN @rownum := 0 
      WHEN @ps = l.team_id 
      THEN @rownum := @rownum + 1 
      ELSE @rownum 
    END AS rank,
    @ps := l.team_id 
  FROM
    `system-leads` l 
  WHERE (l.user = 189905706)  
    AND l.status != "closed" 
  LIMIT 100000000 OFFSET 0) `x`
ORDER BY x.status ASC,
  x.rank ASC,
  x.lead_id ASC 

I wonder why it works in sqlyog and not the others, but I need it to work regardless of what client I am using.

[mac_gyver]

the only thing that comes to mind would be running a query to initialize the user variable(s) to zero so that they are not nulls at the start of the query. perhaps sqlyog automatically does this for you.

[sKunKbad]

the only thing that comes to mind would be running a query to initialize the user variable(s) to zero so that they are not nulls at the start of the query. perhaps sqlyog automatically does this for you.

 

Yes, that was it. Weird that SQLyog does it for me. Thanks!