スキップしてメイン コンテンツに移動

投稿

11月, 2012の投稿を表示しています

Calculate DENSE_RANK for MySQL

In this post, I will show you SQL query for calculateing dense rank for MySQL . You know Oracle and SQL Server have the function called DENSE_RANK, which is for returning rank of the value. Unfortunately MySQL doesn't have the built-in function. We can calculate dense rank by nested select for same table with count function. However this strategy is toooo slow because calculating rank by counting number of rows above the target row for every rows. Anyway here is the query for calculating dense rank for MySQL. SELECT id, @rnk:=IF(@preval <=> score, @rnk, @row + 1) AS dns_rnk, @row:= @row+1 AS rnk, @preval:=score as score FROM table # be careful for NULL handling. # if all the values of score column are null, then dns_rank will zero. # please set proper initial value for @preval based on your data. JOIN (SELECT @rnk := 0, @preval :=null, @row := 0) r ORDER BY score DESC The result should be something like this. id dns_rnk rnk score 1 1 1 100 4 2

Symfony 1.4 Accessing Context

Symfony 1.4 is already a legacy framework. But I think a lot of web system still running on it. In this post, I will show you some quick tips for accessing infromation by using sfContext . sfContext provides almost everything you want access. But you should not use context everywhere too much because sfContext is a kind of global objeect and if a lot of codes depends on context, your code will be hard to be tested or maintained, I think. Basic You can access sfContext by following code. sfContext::getInstance(); In sfFilter, you can access sfContext by the following code. $this->getContext(); What Kind of Fields You can access? Routing name $context->getRouting()->getCurrentRouteName(); User $context->getUser(); Request $context->getRequest(); Response $context->getResponse(); Controller $context->getController(); Logger sfContext::getInstance()->getLogger(); Advanced Use Check if http request is secure (e.g. https request). $co