CODEWITHSUNDEEP
mysqlselectpartition-by
Vikram Singh Shekhawat
Vikram Singh Shekhawat

Reputation: 764

ROW_NUMBER() OVER (PARTITION BY giving Syntax error in mysql5.7

I am using partition by to get duplicate rows and this query returning syntax error in mysql5.7 

select column1,ROW_NUMBER() OVER (PARTITION BY column2, column3 ORDER BY column2 DESC) as  RowNumber 
from tableA

Error: 

ERROR 1064 (42000): 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 column1, column2 ' at line 1

Or any other query

Or any other query that retrun the only rows that duplicates(both column2 and column 3 contains same values repectivley) in this case the output will return row 1, 3, 5, 6

All rows in table: All rows in table

Desired output by query: Desired output by query

Thanks for your help.

Upvotes: 1

Views: 2901

Answers (1)

forpas
forpas

Reputation: 164064

With EXISTS:

select t.* from tablename t
where exists (
  select 1 from tablename
  where column1 <> t.column1 and column2 = t.column2 and column3 = t.column3
)

Upvotes: 1

Related Questions