SQL Top N per group each year without using row_number / rank or variables

Question

I have a limitation of not being able to use window functions or variables due to tool constraints. (Sorry couldn't elaborate much) but is there any scalable way to get Top N per group for each year? I have multiple tables joined.

Example: Table 1 schema (ID, Item_ID, Total, Year) Table 2 schema (ID, name, group, Year)

I have been trying partition by options and variables but facing issue with tool constraint not supporting them. Is there any other suggestions ?

SELECT
    group,
    sales,
    year
FROM
(
    SELECT
        group,
        sum(total) as sales,
        YEAR(CREATED_AT) as year,
        {{rn}} := case({{prev}} = group, {{rn}} + 1, 1) AS rn,
        {{prev}} := group
    FROM (
        SELECT * FROM PRODUCTS p
        LEFT JOIN ORDERS o
        on p.ID = o.PRODUCT_ID ) AS mytable
    LEFT JOIN (SELECT {{prev}} := 0, {{rn}} := 0) AS vars
    ORDER BY group, sales DESC, year
) AS T1
WHERE rn <= 3;

I have tried partition by and rank with over() too but unfortunately those are not supported.

Also tried with nested selects with group = 'xxx' and year = '2020' and UNION ALL for example . But that's not scalable if i have 50 years or groups

score 2 · Answer 1 · edited Jun 13 '23 at 09:20

is there any scalable way to get Top N per group for each year?

Use 2 table copies. Like this:

SELECT group_id, t1.value
FROM test t1
        JOIN test t2 USING (group_id)  -- group == rows with the same group_id
WHERE t1.value <= t2.value     -- the ordering - by value desc.
GROUP BY group_id, t1.value
HAVING COUNT(*) <= 3           -- select top 3 per group
ORDER BY 1,2 DESC

DEMO fiddle

SQL Top N per group each year without using row_number / rank or variables

1 Answers1