1

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.

Vikram Singh Shekhawat
  • 714
  • 1
  • 10
  • 27

1 Answers1

1

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
)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thank for help. What do if I want to skip the first row of duplicated then what modification i need make in this query ? Suppose there is 2 rows duplicated then I want to get only second and if 3 then want to get last 2 by skip first. Thanks – Vikram Singh Shekhawat Apr 15 '20 at 19:08
  • 1
    Something like: `select t.* from tablename t where t.column1 > (select min(column1) from tablename where column2 = t.column2 and column3 = t.column3)` – forpas Apr 15 '20 at 19:28
  • 1
    Thank you so much for such and immediate reply – Vikram Singh Shekhawat Apr 15 '20 at 19:30