CODEWITHSUNDEEP
phpmysql
112233
112233

Reputation: 2466

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

Upvotes: 4

Views: 13003

Answers (1)

spencer7593
spencer7593

Reputation: 108410

EDIT

NOTE: MySQL 8.0 introduces support for analytic window functions which is absent from earlier versions of MySQL.

ORIGINAL 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.

Upvotes: 9

Related Questions