CODEWITHSUNDEEP
mysqlmariadb
Guran
Guran

Reputation: 1

How to use Rank Function in MariaDb

I am trying use the Rank function on MariaDB. I have already created some kind of RANK() implementation on my table but it's not working properly.

Two of the queries I have tried are these:

SELECT SpelarID, RondNr, Rondresultat, RANK() OVER(PARTITION BY TavlingRondNr ORDER BY Rondresultat DESC)
FROM Resultatlista
WHERE RondNr = 1
ORDER BY Rondresultat DESC
LIMIT 10;

And

SELECT *, RANK() OVER (ORDER BY Rondresultat DESC)
FROM Resultatlista
WHERE TavlingRondNr = 1
ORDER BY Rondresultat DESC
LIMIT 10;

The result I get when i run it with phpMyAdmin is this:

MySQL said:

Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

near '(PARTITION BY TavlingRondNr ORDER BY Rondresultat DESC) FROM Resultatlista WHE' at line 1

And for the second one I get a similar error message:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

near '(ORDER BY Rondresultat DESC) FROM Resultatlista WHERE TavlingRondNr = 1 ORDER' at line 1

Any suggestions to fix the SQL. I need to use it in an Update later.

Upvotes: 0

Views: 4786

Answers (2)

user20569976
user20569976

Reputation: 1

RANK()

SELECT pid, name, age, @curRank := @curRank + 1 AS rank FROM players p, ( SELECT @curRank := 0 ) q ORDER BY age DESC, name

DENCE_RANK()

SELECT pid, name, age, CASE WHEN @prevRank = age THEN @curRank WHEN @prevRank := age THEN @curRank := @curRank + 1 END AS rank FROM players p, (SELECT @curRank :=0, @prevRank := NULL) r ORDER BY age

Its working

Upvotes: -1

elenst
elenst

Reputation: 3987

Window functions were first introduced in MariaDB 10.2.0.

They do not exist in (and won't be added to) 5.5, 10.0, 10.1.

As of now, end of January 2017, the latest MariaDB 10.2.3 is a Beta release and is not recommended for production, but a release candidate is expected soon, and GA should not be far away, so it is worth trying.

Upvotes: 3

Related Questions