PHP Versions Question posted in
You can find a list of all versions here.

Php versions – MariaDB Rank Query

I am very new to PHP and SQL and building a project for hockey statistics.

For this page, I am looking to create a SQL query to rank hockey leaders based on goals scored. I am trying to take a table in my database with Team Id, and Goals For where the teams have a League Id = 0 and RANK the Goals For column.

I have tried the following query with no luck.

SELECT
`League Id`, `Team Id`, `Goals For`,
    RANK () OVER (ORDER BY `Goals For` DESC) `GF`
FROM
    `team_records`
    WHERE `League Id` = 0;

Everything I have come up with on the internet leads to a SQL error. How can I fix this? Any help is greatly appreciated.

The error message is:

#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 ‘OVER (ORDER BY Goals For DESC) GF

Tags:

2

Answers


  1. 0

    Try without the RANK to just return totals goals per team in the correct order without rank numbers. Then you can add the row/rank numbers on the front end with PHP:

    SELECT
    `League Id`, `Team Id`, SUM(`Goals For`) `GF`
FROM  `team_records`
GROUP BY `League Id`, `Team Id`
ORDER BY SUM(`Goals For`) DESC
WHERE `League Id` = 0;

    If this works, you probably need to upgrade your database, because it’s a strong indication your database installation is too old for window functions. Worse, this also means it’s old enough it no longer gets security patches. That’s a big deal. It’s important enough to even be worth the hassle of switching hosts.

    Login or Signup to reply.
  2. 0

    The Syntax for RANK.

    This requires:

    • No space after RANK
    • A PARTITION BY clause

    Based on other comments I assume you want to partition by the Team Id to RANK teams like:

    SELECT
`League Id`, `Team Id`, `Goals For`,
    RANK() OVER (PARTITION BY `Team Id` ORDER BY `Goals For` DESC) `GF`
FROM
    `team_records`
    WHERE `League Id` = 0
ORDER BY `League id`;

    ref: https://dbfiddle.uk/JBgzYa2m

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search