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
pimpinhockey is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
11
Try without the RANK to just return totals goals per term without rank numbers in the correct order. 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 an indication your database installation is likely too old for Window functions, which 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.