When querying H2 I get a SQL Syntax Error 42001-197-CodePudding

I am by no means a SQL expert, nor H2, so feel free to drag me through the coals. I have two tables, one called ORDERS and one called PRODUCTS, and I'm trying to show the top three selling categories for each year available in the data.

When running the following SQL:

WITH year_categories AS ( 
    SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS year, 
    PRODUCTS.CATEGORY AS "Category", 
    SUM(ORDERS.TOTAL) AS total_sales 
    FROM ORDERS JOIN PRODUCTS 
    ON ORDERS.PRODUCT_ID = PRODUCTS.ID 
    GROUP BY year, PRODUCTS.CATEGORY 
) 
SELECT year, "Category", total_sales 
FROM ( 
    SELECT year, "Category", total_sales, 
    ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_sales DESC) as row_num
    FROM year_categories 
) sub 
WHERE sub.row_num <= 3 
ORDER BY year, total_sales DESC;

I get the following error:

Blockquote Syntax error in SQL statement " WITH YEAR_CATEGORIES AS ( SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS YEAR, PRODUCTS.CATEGORY AS ""Category"", SUM(ORDERS.TOTAL) AS TOTAL_SALES FROM ORDERS JOIN PRODUCTS ON ORDERS.PRODUCT_ID = PRODUCTS.ID GROUP BY YEAR, PRODUCTS.CATEGORY ) SELECT YEAR, ""Category"", TOTAL_SALES FROM ( SELECT YEAR, ""Category"", TOTAL_SALES, ROW_NUMBER() OVER (PARTITION[*] BY YEAR ORDER BY TOTAL_SALES DESC) AS ROW_NUM FROM YEAR_CATEGORIES ) SUB WHERE SUB.ROW_NUM <= 3 ORDER BY YEAR, TOTAL_SALES DESC; "; expected ")"; SQL statement: WITH year_categories AS ( SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS year, PRODUCTS.CATEGORY AS "Category", SUM(ORDERS.TOTAL) AS total_sales FROM ORDERS JOIN PRODUCTS ON ORDERS.PRODUCT_ID = PRODUCTS.ID GROUP BY year, PRODUCTS.CATEGORY ) SELECT year, "Category", total_sales FROM ( SELECT year, "Category", total_sales, ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_sales DESC) as row_num FROM year_categories ) sub WHERE sub.row_num <= 3 ORDER BY year, total_sales DESC; [42001-197]

What am I doing wrong? I've tried inserting ")" in a couple of different locations after ROW_NUM, with no different result. Alternatively, is there an easier or better way to structure this query?

Thank you for any help in advance!

CodePudding user response：

The most obvious explanation is that your version/mode of H2 doesn't support common table expressions. You may try just inlining everything. Also, your query can be simplified:

SELECT year, Category, total_sales 
FROM
(
    SELECT EXTRACT(YEAR from o.CREATED_AT) AS year, 
           p.CATEGORY AS Category,
           SUM(o.TOTAL) AS total_sales,
           RANK() OVER (PARTITION BY EXTRACT(YEAR from o.CREATED_AT)
                        ORDER BY SUM(o.TOTAL) DESC) rnk
    FROM ORDERS o
    INNER JOIN PRODUCTS p ON o.PRODUCT_ID = p.ID 
    GROUP BY year, Category
) t
WHERE rnk <= 3;

I switched from ROW_NUMBER() to RANK(), but you might also want to use DENSE_RANK() instead, depending on your requirements.

CodePudding user response：

You're using an entirely outdated and unsupported H2 1.4.197. This version doesn't support window functions.

You need to upgrade to some modern version of H2.

Please note that YEAR is a keyword in them, so you need to quote it. Alternatively you can add ;NON_KEYWORDS=YEAR to JDBC URL as a temporary workaround.