Mysql Reset variable after execute every row

Question

Here is my current mysql statement:

SET @rownum := 0;
SELECT school_id, 
       mb_no AS student_id,
       xp, 
       @rownum := @rownum + 1 AS rank_school_position
FROM users 
WHERE school_id IN (
       SELECT school_id 
       FROM users) 
ORDER BY xp DESC;

Here is sample table

CREATE TABLE `users` (
`school_id` INT(11) NOT NULL DEFAULT '0',
`mb_no` INT(11) NOT NULL DEFAULT '0',
`xp` INT(10) NOT NULL DEFAULT '0')

and my sample data

INSERT INTO `d_copy` (`school_id`, `mb_no`, `xp`) VALUES
(610, 1, 1190),
(610, 2, 195),
(611, 3, 0),
(610, 4, 35),
(610, 5, 160),
(611, 6, 0),
(610, 7, 175),
(611, 8, 0),
(610, 9, 95),
(610, 10, 4770);

How I can reset back @rownum to 0 on every SELECT school_id FROM users executed?

My expected output will be like this

enter image description here

Please edit your question and provide sample data and desired results. The explanation is not clear. — Gordon Linoff, Apr 17 '15 at 01:50
@GordonLinoff [here](http://stackoverflow.com/a/16715618/4020264) you gave warning about using variables inside queries, does it still stand? — 1010, Apr 17 '15 at 03:23
this isn't the same thing as that, @GordonLinoff was referring to `select @x, @x := @x +1`. the first `@x` may have the old value, or the new, its indeterminate. — pala_, Apr 17 '15 at 08:52
Also - i don't see how your sample data can arrive at your expected output. What would be your expected output for your given data? — pala_, Apr 17 '15 at 08:54
I modified the sample data and the expected output data @pala_ — Nere, Apr 17 '15 at 09:08
awesome thats what i was hoping it would be - the answer is totally different now :) — pala_, Apr 17 '15 at 09:08
@1010 . . . Yes. That is not a warning about using variables in general. It is a warning about having two separate expressions in the `select` that reference the same variable. MySQL does not guarantee the order of evaluation, so handling multiple variables is tricky. — Gordon Linoff, Apr 17 '15 at 23:21

pala_ · Answer 1 · 2015-04-18T00:20:33.337

2

~~move the variable initialisation into the from clause:~~

SELECT school_id, 
       mb_no AS student_id,
       xp, 
       @rownum := @rownum + 1 AS rank_school_position
FROM users, (SELECT @rownum := 0) q
WHERE school_id IN (
       SELECT school_id 
       FROM users) 
ORDER BY xp DESC;

edit

I just had an epiphany and I think i figured out what you want. Here is your answer:

SELECT school_id, 
       mb_no AS student_id,
       xp, 
       if(@prevschool = school_id, 
          @rownum := @rownum + 1, 
          case when @prevschool := school_id then @rownum := 1 end) AS rank
FROM users
WHERE school_id IN (
       SELECT school_id 
       FROM users) 
ORDER BY school_id asc, xp DESC;

sqlfiddle

On another look - the entire where clause is redundant and can be removed. Also as pointed out in comments it wouldn't handle the (weird) case of a school_id of 0

SELECT school_id, 
       mb_no AS student_id,
       xp, 
       if(@prevschool = school_id, 
          @rownum := @rownum + 1, 
          case when @prevschool := school_id then @rownum := 1 else @rownum := 1 end) AS rank_school_position
FROM users
ORDER BY school_id asc, xp DESC;

edited Apr 18 '15 at 00:20

answered Apr 17 '15 at 01:53

pala_

8,901
1
15
32

at SELECT SET @rownum := 0 – Nere Apr 17 '15 at 03:10
@ImranHamzah sorry, fixed that. this query gives the exact result as yours tho. – pala_ Apr 17 '15 at 03:10
@ImranHamzah i finally figured out what you really want - the second query in my answer should give the results you want. – pala_ Apr 17 '15 at 09:07
@pala_ . . . Your revised form is *almost* correct. A problem can occur when `school_id` takes on the value of 0 or NULL, the `then` statement is not executed. For the way you do this, you should have `else @rownum := 1`. However, I personally don't like mixing `if()` and `case`. – Gordon Linoff Apr 17 '15 at 23:23
@GordonLinoff it will never be `null` as it has a `not null` constraint. If it has a `0` value i'd say it's the data thats broken not the query, but since its trivial to support we may as well. as for mixing `case` and `if` - my assumption was (clearly) that `school_id` would always be valid, and the `else` case would never arrive. anyway, suggestion included and redundant `where` clause alsoremoved. – pala_ Apr 18 '15 at 00:23
1

@pala_ . . . Even with the `not null` constraint, I think the `else` should be included. Someone reading the code may not be aware of the `null` constraint on the table, so this looks like a potential bug. Time wasted investigating that is just . . . time wasted. – Gordon Linoff Apr 18 '15 at 01:56

Mysql Reset variable after execute every row

1 Answers1