phpmyadmin Question posted in
The official documentation can be found here.

I have no idea why these SQL command doesnt work – Phpmyadmin

So I have an Xampp server with phpmyadmin and I usered this command, which always worked:

WITH temptable AS (SELECT *, ROW_NUMBER() OVER (ORDER BY character_ID DESC) 
AS t FROM characters) SELECT * FROM temptable WHERE t BETWEEN 0 AND 10;

Now I got a database on a real server and it doesent work anymore. So I looked for other commands on the internet and always found this one on several websites:

SELECT 
  ROW_NUMBER() OVER(ORDER BY Character_ID ASC) AS t,
  Charactername
FROM characters WHERE t < 5;

I looked over several websites and it doesnt work for me and i dont know why.
The syntax in Phpmyadmin doesnt mark anything wrong when i write this command but i got still the error:
You have an error in your SQL syntax;

Maybe someone knows why?

Tags:

2

Answers


  1. 0

    Filtering aggregation doesn’t work that way in select.Modify your query as below,

    SELECT * FROM
(
SELECT 
  ROW_NUMBER() OVER(ORDER BY Character_ID ASC) AS t,
  Charactername
FROM characters) Characternametab
 WHERE t < 5;
    Login or Signup to reply.
  2. 0

    You cannot filter by columns defined in the SELECT. If you are using MySQL (or MariaDB), you can use the HAVING clause:

    SELECT ROW_NUMBER() OVER(ORDER BY Character_ID ASC) AS t,
       Charactername
FROM characters 
HAVING t < 5;

    These databases extend the use of the HAVING clause for non-aggregation queries, and it allows the use of column aliases for filtering.

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

Back To Top
Search