CODEWITHSUNDEEP
mysqlsql
CDN
CDN

Reputation: 95

mysql sql error when using the row_number

This query is throwing error, it seems correct but i am getting an error 

WITH Rows AS (SELECT *,
ROW_NUMBER() OVER (ORDER BY userid asc) as [Row]
FROM users 
WHERE 1=1 
)
SELECT * FROM Rows
WHERE Row >= 1 and Row <= 10

error i am getting while running the above statement is: 

 1064 - 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 'Rows AS (SELECT *, ROW_NUMBER() OVER (ORDER BY userid asc)
  FROM users 
  ' at line 1

Upvotes: 1

Views: 2005

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your error is on Rows because MySQL does not recognize the CTE.

Then, MySQL also does not recognize [, so you want to use a more reasonable name. In MySQL 8+, that would be something like:

WITH Rows AS (
      SELECT *,
      ROW_NUMBER() OVER (ORDER BY userid asc) as seqnum
      FROM users 
)
SELECT *
FROM Rows
WHERE seqnum <= 10;

You don't need to compare to 1. That is definitional.

Of course, this, in turn, is overkill. The CTE is unnecessary:

SELECT *,
       ROW_NUMBER() OVER (ORDER BY userid asc) as seqnum
FROM users 
ORDER BY userid
LIMIT 10;

But this will still fail, because MySQL versions started recognizing CTEs and window functions in the same version.

So, you can just use variables:

SELECT u.*, (@rn := @rn + 1) as seqnum
FROM users u CROSS JOIN
     (SELECT @rn := 0) params
ORDER BY userid
LIMIT 10;

Upvotes: 1

Related Questions