Keren Keren - 6 months ago 20
SQL Question

PARTITION BY not working

enter image description here

I want to create menu with sub menus. Therefore all I need to do is display

menu_cat
as Main menu (Not repeating) while
menu
be the sub menu for related
menu_cat
.

Clauses like DISTINCT and GROUP BY appear to work on entire rows. As a result of my search , I came across this link: DISTINCT for only one Column

This is exactly what I'm trying to do. However I'm getting error. Below is my query and Phpmyadmin error. Please help me to fix this error.

$query = "Select * FROM ('SELECT menu_cat,menu,manu_href,ROW_NUMBER() OVER(PARTITION BY menu_cat ORDER BY menu_id DESC) rn FROM menu')a WHERE rn = 1";


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 ''SELECT menu_cat,menu,manu_href,ROW_NUMBER() OVER(PARTITION BY
menu_cat ORDER BY' at line 1


$query2 = "select menu_cat OVER (PARTITION BY menu_id) AS Cat,menu,menu_href from menu"


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 menu_id) AS Cat,menu,menu_href from menu LIMIT 0,
30' at line 1

Answer

MySQL doesn't support "analytic functions" like ROW_NUMBER() OVER, RANK(), etc.

Those functions are available in SQL Server, Oracle and other databases.

But not MySQL.

Also, single quotes enclose a string literal. So this:

SELECT * FROM ('SELECT ... ')  

is invalid, because a string literal isn't valid in that context... no matter how much the contents of the string look like a SELECT statement.


In MySQL, we can sometimes use user-defined variables to emulate that type of functionality.

  SELECT v.menu_cat
       , v.menu
       , v.manu_href  
    FROM ( SELECT @rn := IF(m.menu_cat = @prev_menu_cat,@rn+1,1) AS rn
                , @prev_menu_cat := m.menu_cat                   AS menu_cat
                , m.menu
                , m.manu_href
             FROM (SELECT @prev_menu_cat := NULL, @rn := 0) i
            CROSS
             JOIN menu m
            ORDER
               BY m.menu_cat
                , m.menu_id DESC
         ) v
   WHERE v.rn = 1

The MySQL Reference Manual warns that this behavior of user-defined variables is undefined.