Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: User defined variables and spring JDBC

    I have a table similar to this:
    ______________
    NAME--------AGE
    ----------------------
    Tom----------20
    Dick----------20
    Harry---------20
    Jimmy--------24
    Johny--------24
    Mickey-------27
    Moe----------27

    And this is what I expect::

    _________________________
    NAME--------AGE------ROWNUM
    ----------------------------------------
    Tom----------20-----------1
    Dick----------20----------2
    Harry---------20-----------3
    Jimmy--------24-----------1
    Johny--------24-----------2
    Mickey-------27-----------1
    Moe----------27-----------2

    Which is basically a rownumber over a 'group by age'. The query which seemed to work first looks like:

    Code:
    select g.name,g.age,g.ROWNUM from(
    SELECT  name, age, 
    @running:=if(@previous=age,@running,0) + 1 as ROWNUM,
    @previous:=age from test t
    )g order by age
    So this worked in my SQL editor, but when I run it from my spring JDBC code, it returns this:
    _________________________
    NAME--------AGE------ROWNUM
    ----------------------------------------
    Tom----------20-----------1
    Dick----------20----------1
    Harry---------20-----------1
    Jimmy--------24-----------1
    Johny--------24-----------1
    Mickey-------27-----------1
    Moe----------27-----------1

    Is this because of the fact that I'm setting and reading user defined variables in the same select? Or is it something to do with spring JDBC's handling of user variables??

    Would really appreciate some help guys!

  2. #2
    Join Date
    Jul 2009
    Posts
    44
    UPDATE:
    Eliminate the spring JDBC bug as a possible reason. Ran the same query on another SQL editor and found that on the first execution post connecting to the database, it gives me the same wrong output. Clearly its the user defined variables being set and read in the same select causing this. Now I'm left wondering how can I decouple this into two separate statements

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try this:

    Code:
    mysql> set @rownum:=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set @age:=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select name, age, if(@age=age, @rownum:=@rownum+1, @rownum:=1) rownum, @age:=age from names order by age, name;
    +--------+------+--------+-----------+
    | name   | age  | rownum | @age:=age |
    +--------+------+--------+-----------+
    | Dick   |   20 |      1 |        20 | 
    | Harry  |   20 |      2 |        20 | 
    | Tom    |   20 |      3 |        20 | 
    | Jimmy  |   24 |      1 |        24 | 
    | Johnny |   24 |      2 |        24 | 
    | Mickey |   27 |      1 |        27 | 
    | Moe    |   27 |      2 |        27 | 
    +--------+------+--------+-----------+
    7 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jul 2009
    Posts
    44
    Thanks a ton! that worked. However, Aren't we still setting the same user variable and selecting them in a single select? we just just moved out the initialization to a separate statement, but the incrementing logic (setting) still remains.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •