Query Failed with partition. Help required

Question

0.00/5 (No votes)

See more:

MySql version 3.5.2.2

Fail to run this query.

SQL

select *
from (
select *, row_number() over (partition by `Column1` order by `Column2` desc) VersionRank from Table1
 ) T
 where `VersionRank` = 1

Error Ocurred:
#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 '(partition by `Column1` order by `Column2` desc) VersionRank from Table1' at line 3

Posted 12-Jan-15 3:25am

DoingWork

Add a Solution

Comments

Richard Deeming 12-Jan-15 9:34am

I don't think MySql supports the row_number function. The only results I can see on Google are describing how to emulate it with a variable - for example, http://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/[^]

EDIT: Whoops, just noticed digimanus' updated answer.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Answer 1 · 2015-01-12T03:28:00

Solution 1

Only in MSSQL:

SQL

SELECT *
FROM (
		SELECT row_number() over (partition by Column1 order by Column2 desc) VersionRank, * 
		FROM Table1
 ) T
 WHERE T.VersionRank = 1

for MySQL read this[^]

Posted 12-Jan-15 3:28am

Herman<T>.Instance

Updated 12-Jan-15 3:30am

v2

Comments

CHill60 12-Jan-15 9:30am

Good spot

DoingWork 12-Jan-15 10:08am

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 '(partition by Column1 order by Column2 desc) VersionRank, * FROM table1 at line 3

Query Failed with partition. Help required

1 solution

Solution 1

Add your solution here

Preview 0

Query Failed with partition. Help required

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us