SQL ROW_num function

Question

I am running the below query and getting the following error -

MySQL Database Error: 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 STUDY_SITE_ID ORDER BY VISIT_START_DATE DESC) AS cnt FROM DESIRE' at line 1 3 28

Query -

SELECT *, ROW_NUMBER() OVER (PARTITION BY STUDY_SITE_ID ORDER BY VISIT_START_DATE DESC) AS cnt 
 FROM TABLE_a

ROW_NUMBER is supproted starting from MySQL 8.0 - https://stackoverflow.com/a/46753800/5070879. You could check version by running `select @@version;` — Lukasz Szozda, Mar 04 '19 at 17:12

Caius Jard · Answer 1 · 2019-03-05T06:14:07.423

I suspect the comment is correct and your MySQL doesn't support rownumber

You can fake it with a self join if you have numeric/incremental ids, by joining the table on id <= id and grouping/counting the result. If you want a partition, join on partioncol = partitioncol and id <= id

You can also use in-query variables in a pattern like this:

SELECT
  t.*, 
  @r := @r + 1 AS rn
FROM
  t, 
  (SELECT @r := 0) x

You can get more funky with this if you need a partition by col:

SELECT
  t.*, 
  @r := CASE 
    WHEN col = @prevcol THEN @r + 1 
    WHEN (@prevcol := col) = null THEN null
    ELSE 1 END AS rn
FROM
  t, 
  (SELECT @r := 0, @prevcol := null) x
ORDER BY col

Order of assignment of prevcol is important - prevcol has to be compared to the current row's value before we assign it a value from the current row (otherwise it would be the current rows col value, not the previous row's col value). The MySQL documentation states that the order of evaluation of select list items isn't guaranteed so we need a way to guarantee that we will first compare the current row's col to prevcol, then we'll assign to it

To do this we use a construct that does have a guaranteed order of execution: the case when

The first WHEN is evaluated. If this row's col is the same as the previous row's col then @r is incremented and returned from the CASE, and stored in @r. The assignment returns the new Blair of @r into the result rows.

For the first row on the result set, @prevcol is null so this predicate is false. This predicate also returns false every time col changes (current row is different to previous row). This causes the second WHEN to be evaluated.

The second WHEN is always false, and it exists purely to assign a new value to @prevcol. Because this row's col is different to the previous row's col, we have to assign the new value to keep it for testing next time. Because the assignment is made and then the result of the assignment is compared with null, and anything equated with bill is false. This predicate is always false. But it did its job of keeping the value

This means in situations where the partition by col has changed, it is the ELSE that gives a new value for @r, restarting the numbering from 1

You should not assign variables in one expression and then use them in another. The documentation specifically warns against this. — Gordon Linoff, Mar 04 '19 at 18:57

score 0 · Answer 2 · answered Mar 04 '19 at 18:57

You can use variables . . . but it is very important that the assignment be within a single expression. So:

SELECT a.*,
       (@rn := if(@ss = a.STUDY_SITE_ID, @rn + 1,
                  if(@ss := a.STUDY_SITE_ID, 1, 1)
                 )
       ) as cnt
FROM (SELECT a.*
      FROM TABLE_a a
      ORDER BY a.STUDY_SITE_ID, a.VISIT_START_DATE DESC
     ) a CROSS JOIN
     (SELECT @ss := -1, @rn := 0) params

SQL ROW_num function

2 Answers2