0

I use a user-defined variable in MySQL to group zip codes. Now I only want to select results with column rownum equal to 1. How to select by rownum in WHERE clause by alias rownum?

Following query gives me the desired results ..

SET @row := 0;
SELECT  
  (CASE WHEN @plz <> user.plz THEN @row := @row +1 ELSE @row END) AS rownum, @plz:= user.plz, uid
             
FROM 
  (SELECT @row := 0) AS var,
  user

Results:

rownum    plz    uid 
1         2378   92014
1         2378   87323
2         6766   91344
2         6766   90342
3         9999   34353

Now, I only want to select by specific rownum. How to select by alias rownum?

SELECT  
     (CASE WHEN @plz <> user.plz THEN @row := @row +1 ELSE @row END) AS rownum, @plz:= user.plz, uid
                     
   FROM 
     (SELECT @row := 0) AS var,
     user 
        
   WHERE rownum=1

does not work, why? How to select data by alias rownum only?

IBRAHIM
  • 77
  • 9
Aldee
  • 61
  • 9
  • Use your query as subquery. Apply WHERE in outer query. – Akina Aug 04 '20 at 12:37
  • Pay attention! Your query does not contain ORDER BY clause so the query output is indefinite (and it may alter in any moment). Add `ORDER BY user.plz`. – Akina Aug 04 '20 at 12:39
  • Why does it not work in the inner query, could you explain? – Aldee Aug 04 '20 at 12:41
  • Output rowset is calculated AFTER applying WHERE conditions. So you cannot even refer to the output alias in WHERE. You may try to move the condition from WHERE to HAVING - but I don't know does the output will be correct. – Akina Aug 04 '20 at 12:45
  • I have tested - moving the condition to HAVING gives incorrect output. Non-applicable. – Akina Aug 04 '20 at 12:54
  • What you are trying to do might not even need your MySQL @ variable attempts. Can you please explain the context of what you are looking for, simple English. From my interpretation, you have a game with players... Each player in their own zip code, and you are trying to compare to the specific user's zip code. Do you only want one other player's zip code that is other than the user? Please EDIT your existing post and add that clarification. You might just need a select distinct / min() by zip codes other than the user... but again EDIT your post, don't answer in comment. – DRapp Aug 04 '20 at 13:15
  • With outer query it works, thanks! – Aldee Aug 04 '20 at 17:35

0 Answers0