RE : Using a user variable in a request with MySQL

[Gilles SCHLIENGER <gi...@cncc.fr>]

Thanks Larry,

As far as I know, this is the standard workaround in MySQL to get the ROW_NUM of the resultset.

For example, this would return:
1,190
2,560
3,989

I actually need this to retrieve that number in a more complex query with joins

Thanks
Gilles

________________________________________
De : Larry Meadors [larry.meadors@gmail.com]
Date d'envoi : jeudi 11 juin 2009 15:58
À : user-java@ibatis.apache.org
Objet : Re: Using a user variable in a request with MySQL

On Thu, Jun 11, 2009 at 7:42 AM, Gilles
SCHLIENGER<gi...@cncc.fr> wrote:
>
> SET @NUM=0;
> SELECT (@NUM:=@NUM+1), JET_ID FROM JETON
>    WHERE JET_FK_ABO_ID = 3;
>

Sorry, I'm not really up on mysql - what is this supposed to do?

Larry

RE : RE : Using a user variable in a request with MySQL

[Gilles SCHLIENGER <gi...@cncc.fr>]

It works great with iBATIS/MySQL.

Thanks a lot Larry and sorry for bothering you with something I could/should have found myself...

Regards
Gilles

________________________________________
De : Larry Meadors [larry.meadors@gmail.com]
Date d'envoi : jeudi 11 juin 2009 17:22
À : user-java@ibatis.apache.org
Objet : Re: RE : Using a user variable in a request with MySQL

I see, try this:

SELECT (@rownum:=@rownum+1), JET_ID
FROM JETON, (SELECT @rownum:=0) r
WHERE JET_FK_ABO_ID = 3;

Found it here: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/

It might work, I don't have a mysql install handy to try it on. :-/

Larry

Re: RE : Using a user variable in a request with MySQL

[Larry Meadors <la...@gmail.com>]

I see, try this:

SELECT (@rownum:=@rownum+1), JET_ID
FROM JETON, (SELECT @rownum:=0) r
WHERE JET_FK_ABO_ID = 3;

Found it here: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/

It might work, I don't have a mysql install handy to try it on. :-/

Larry