0

I have a problem with MySQL user defined variables!

My table looks like this ..

plz    |    uid
4000        1
4000        2
9000        3
7000        4

Desired output ..

rownum  |  plz    |    uid
1000       4000         1
1000       4000         2
999        9000         3
998        7000         4

I want to SELECT a non existing column rownum as a flag column, along with plz and uid column. If the value in plz column changes, rownum column should be decreased by 1. All the same plz must have same rownum. plz is unique.

My approach ..

SELECT   
  (CASE WHEN @plz <> user.plz THEN @row := @row -1 ELSE @row END) AS rownum, @plz:= user.plz, uid
                                 
FROM             
  (SELECT @row := 1001) r, user
LEFT JOIN 
  geodb_locations ON user.plz = geodb_locations.id 
WHERE 
  user.plz IN(29386,30013,29271,30406)         
ORDER BY 
  FIELD(user.plz,29386,30013,29271,30406), 
  uid DESC

This query works, but only at second call. First call all values of rownum are set to 1001. How to change the CASE WHEN statement that it works at first call? Any ideas?

Thanks in advance!!

Edit QUERY

SELECT   
            @prev as previous, @prev := u.plz as current, CASE WHEN @prev <> u.plz THEN @row := @row -1 ELSE @row END AS rownum,
            uid
                                 
            FROM             
                (SELECT @prev := NULL, @row := 1001) as r, user as u
            LEFT JOIN 
                geodb_locations ON u.plz = geodb_locations.id 
            WHERE 
                u.plz IN(29386,30013,29271,30406) AND
                freigeben=1 AND 
                uid != 97548          
            ORDER BY 
                FIELD(u.plz,29386,30013,29271,30406), 
                uid DESC

The output is correct but the CASE WHEN seems to fails!? rownum is always 1001! Even if @prev is NOT EQUAL. Any ideas? Where is my fault?

Output

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1001 | 92014 |
| 29386    |   29386 |   1001 | 87223 |
| 29386    |   29386 |   1001 | 83160 |
| 29386    |   29386 |   1001 | 81322 |
| 29386    |   29386 |   1001 | 81293 |
| 29386    |   29386 |   1001 | 81259 |
| 29386    |   29386 |   1001 | 34213 |
| 29386    |   29386 |   1001 | 34026 |
| 29386    |   29386 |   1001 | 32851 |
| 29386    |   29386 |   1001 | 32668 |
| 29386    |   29386 |   1001 | 32358 |
| 29386    |   29386 |   1001 | 31338 |
| 29386    |   29386 |   1001 | 27765 |
| 29386    |   29386 |   1001 | 26254 |
| 29386    |   29386 |   1001 | 23474 |
| 29386    |   29386 |   1001 | 23130 |
| 29386    |   29386 |   1001 | 20512 |
| 29386    |   29386 |   1001 | 14864 |
| 29386    |   29386 |   1001 | 10951 |
| 29386    |   29386 |   1001 |  8045 |
| 29386    |   29386 |   1001 |  6921 |
| 29386    |   29386 |   1001 |  4459 |
| 29386    |   29386 |   1001 |  3152 |
| 29386    |   29386 |   1001 |  1939 |
| 29386    |   30013 |   1001 | 65193 | <-- @prev <> u.plz why NO decreasing `rownum`
| 30013    |   29384 |   1001 | 12883 |
| 29384    |   29385 |   1001 | 32667 |
| 29385    |   29232 |   1001 | 86641 |
| 29232    |   29481 |   1001 | 94521 |
| 29481    |   29481 |   1001 | 33950 |
| 29481    |   29481 |   1001 | 33926 |
| 29481    |   29667 |   1001 | 16027 |
| 29667    |   29667 |   1001 |   496 |
| 29667    |   29355 |   1001 | 96013 |
+----------+---------+--------+-------+
Aldee
  • 61
  • 9
  • Why not match the tables in the question with the query and show example data. In the query the `@plz` is uninitialized. – slaakso Aug 04 '20 at 17:56
  • There is only one table .. plz is initialized in the select @plz:= user.plz ? – Aldee Aug 04 '20 at 18:03
  • The two tables in the query are `geodb_locations` and `user` and the `@plz` assignment happens after it has been used in `CASE WHEN @plz <>`. – slaakso Aug 04 '20 at 18:09
  • tried to initialize before CASE WHEN in the select without changes .. if i put @plz := user.plz at the beginning of the select, second call fails! – Aldee Aug 04 '20 at 18:22
  • how to match the tables? how could i join the tables to get a third column rownum if the plz has changed flagged with decreasing number starting at 1000 for example? I want to group flag the plz. each row with the same plz should get unique number as rownum. the plz itself are unique and ordered – Aldee Aug 04 '20 at 18:44

0 Answers0