RANK query using PARTITION BY fails in mysql

Question

I tried below query to partition by but which fails with below query, the inner query works

select  issueid, task_type, assignee, timeoriginalestimate, CREATED,
dense_rank() over ( partition by issueid order by CREATED desc ) as rank
       from( 
       --- Complex query with p.pname, i.issuenum, cg.issueid, it.pname task_type, i.assignee, i.timeoriginalestimate, cg.CREATED, columns which works fine
       )

Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( partition by issueid order by CREATED desc ) as rank from( SELECT p.pna' at line 3

Update:

SELECT VERSION(); -- 5.6.27

Thanks for the reply, I updated post with version.pls, check. — sunleo, Jun 01 '20 at 06:22
This might help: https://stackoverflow.com/questions/52352877/mysql-5-6-dense-rank-like-functionality-without-order-by — Nick, Jun 01 '20 at 06:32

mkRabbani · Accepted Answer · 2020-06-01T09:42:00.743

Although your MySQL version do not support Window function, I am letting this is not the issue. Guess you have a higher version and window function is supported.

Now, in your query you have defined the Alias of a column name to "Rank" which is a reserved keyword for your database and you can not use that as column name.

Hope this below hints will help you-

select  
issueid, 
task_type, 
assignee, 
timeoriginalestimate, 
CREATED,
dense_rank() over ( partition by issueid order by CREATED desc ) as rn -- change the alias name
from( 
   -- Your subquery
) A -- Also need to give a Alias name to your sub query

Finally, if you have lower version check this LINK for help to get an idea of creating Row_number or Ranking for MySQL older versions.

In addition, this following sample query will really help you finding solution for different type row_number in mysql-

SET @simple_row_number := 0;
SET @id_wise_row_number := 0;
SET @dense_rank_per_id := 0;
SET @prev := 0;

SELECT *,

@simple_row_number := @simple_row_number + 1 AS simple_row_number,
@id_wise_row_number := IF(issueid > @prev, @id_wise_row_number + 1, @id_wise_row_number) AS id_wise_row_number,
@dense_rank_per_id :=IF(issueid > @prev,1, @dense_rank_per_id + 1) AS dense_rank_per_id,

@prev := A.issueid Prev_IssueId

FROM (
    SELECT 1 issueid, '20200601' CREATED UNION ALL
    SELECT 1 issueid, '20200401' CREATED UNION ALL
    SELECT 1 issueid, '20200501' CREATED UNION ALL
    SELECT 1 issueid, '20200201' CREATED UNION ALL
    SELECT 1 issueid, '20200301' CREATED UNION ALL
    SELECT 2 issueid, '20200301' CREATED UNION ALL
    SELECT 2 issueid, '20200201' CREATED UNION ALL
    SELECT 2 issueid, '20200401' CREATED
) A
ORDER BY issueid, CREATED DESC

@sunieo Thanks for accepting my answer with an up vote. I have also added some sample script for achieving different type of row_number and dense rank. Hope this will help you a lot. — mkRabbani, Jun 01 '20 at 09:44
@suneio . . . You accepted this answer but you should know that MySQL documentation *explicitly* warns against assigning a variable in one expression and using it in another. MySQL does NOT guarantee the order of evaluation of expressions in a `SELECT`: "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. ": https://dev.mysql.com/doc/refman/5.7/en/user-variables.html. — Gordon Linoff, Jun 01 '20 at 12:00

RANK query using PARTITION BY fails in mysql

1 Answers1