iDatamining.org

I am looking for projects to work on
Please contact with me at yiyu.jia@iDataMining.org!

Wednesday, August 4, 2010

Using user defined variable to simulate DB2 RANK function failure in MySQL server

I recently tried to simulate RANK function from DB2 by using user defined variable. It is very nature for developer to think about using MySQL user defined variable as counter to simulate RANK function. However, it is not successful. I got random rank number in the result set. Below is the sample SQL code.
SET @jia = NULL ;
SELECT id, sum( sales ) AS sales, count( * ) AS sig, @jia := IFNULL( @jia , 0 ) +1 AS rank
FROM brandpromo
GROUP BY id
ORDER BY sales DESC
LIMIT 100 ;
I was only aware that the user defined variable is only connection session scope alive. I did not know that it is dangerous to use user defined variables together with GROUP BY and ORDER BY clause. So, DO NOT use user defined variable together with GROP BY and ORDER BY in MySQL server. At least, be careful when you want to use it in that way.

Actually, I found a blog post that talks about this topic in detail. For more info, we can read this.

No comments:

Post a Comment