0
WITH temp_user AS (
SELECT * ,ROW_NUMBER() over (partition by departmentName order by failureTime desc ) as 'row_number'
 from user  )
 SELECT * from temp_user where row_number <= 2

I have sql syntax ,error 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 '<= 2' at line 4

I want to group by department ,then get every group top 2 user. thank you I change the describe ,then i get the right result .but i don't know why the first sql can't work .

select * from (
  SELECT * ,ROW_NUMBER() over (partition by departmentName order by failureTime desc ) as 'row_number'
 from user ) as b where b.row_number <=2
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
Ants-double
  • 29
  • 4
  • 1
    ROW_NUMBER (R); added in 8.0.2 (reserved) https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R , What version are you on select version(); – P.Salmon May 18 '23 at 06:29
  • 1
    Row_number is reserved; name it something else or put backticks around it – ysth May 18 '23 at 06:40

0 Answers0