1

I was hoping to use a ranking query, but can’t seem to understand why my query isn’t working with redash.

SELECT
	p.name,
    name_length,
    name_rank,
    ranked.name
FROM (
	SELECT 
		id,
		name,
		product_id,
		length(name) AS name_length,
		@name_rank := IF(@current_name = name, @name_rank + 1, 1) AS name_rank,
		@current_name := name
	FROM test_table AS t
	ORDER BY 3,4 DESC
    ) ranked
JOIN product AS p ON p.id = ranked.product_id
WHERE name_rank <= 2;

This query works for me using MYSQL Workbench. What I am returned in redash only shows 1.00 as the description rank for each row. Oddly enough, I accidentally ran the whole query followed by the sub query and the description rank actually worked (i.e. name_rank = 1, 2, etc…). Not exactly sure what is going on, but it feels like @name_rank is created for each iteration.

Ran this all by accident as stated above:

SELECT
	p.name,
    name_length,
    name_rank,
    ranked.name
FROM (
	SELECT 
		id,
		name,
		product_id,
		length(name) AS name_length,
		@name_rank := IF(@current_name = name, @name_rank + 1, 1) AS name_rank,
		@current_name := name
	FROM test_table AS t
	ORDER BY 3,4 DESC
    ) ranked
JOIN product AS p ON p.id = ranked.product_id
WHERE name_rank <= 2;

SELECT 
		id,
		name,
		product_id,
		length(name) AS name_length,
		@name_rank := IF(@current_name = name, @name_rank + 1, 1) AS name_rank,
		@current_name := name
	FROM test_table AS t
	ORDER BY 3,4 DESC;

Any help would be greatly appreciated and thanks in advance.

2

I didn’t realize you could run dynamic SQL with the mysql connector :upside_down_face:

Generally here I’d use the ROW_NUMBER() function instead: https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/