Creating a rank on a calculated field?

[KitCarl]

Query works except when adding the @rank :=IF(@prev_val != AKC, @rownum, @rank )AS rank, section which returns error code 1054 - Unknown column 'AKC' in 'field list'

The AKC field is a calculated field, is this the problem? How can I create a rank column where if the AKC field is equal the rows share the same rank.

 

SET @rownum = 0,
@rank = 0,
@prev_val = NULL;

SELECT
@rownum := @rownum + 1 AS ROW,
@rank :=IF(
@prev_val != AKC,
@rownum,
@rank
)AS rank,

TrialListing.qualifier,
CONCAT_WS(
	' ',
	pedigree.pretitle,
	pedigree.`Name`
)AS NAME,
SUM(
	CASE
	WHEN placement.place_id < 5 THEN
		TRUNCATE(
			TrialClass.number_of_entrants / placement.place_id,
			2
		)
	WHEN placement.place_id = 5 THEN
		''
	ELSE
		0
	END
)AS AKC,

SUM(
CASE
WHEN placement.place_id = 1 THEN
	30
WHEN placement.place_id = 2 THEN
	15
WHEN placement.place_id = 3 THEN
	10
WHEN placement.place_id = 4 THEN
	5
END
)AS 'Better Beagling',
SUM(
CASE
WHEN placement.place_id = 1 THEN
	10
WHEN placement.place_id = 2 THEN
	8
WHEN placement.place_id = 3 THEN
	6
WHEN placement.place_id = 4 THEN
	4
WHEN placement.place_id = 0 THEN
	2
END
)AS ibga_licensed

FROM
TrialClass
LEFT JOIN TrialListing ON TrialListing.listingID = TrialClass.listingID
JOIN placement ON placement.event_id = TrialClass.trialClassID
JOIN pedigree ON pedigree.PedigreeId = placement.hound_id
LEFT JOIN place ON place.place_id = placement.place_id
WHERE
TrialListing.qualifier = '1'
AND TrialClass.classID IN('2', '5')
AND pedigree.Sex = 'F'
GROUP BY
NAME
ORDER BY
AKC DESC,
NAME

[fenway]

That's correct -- you can't use aggregated expressions for user-variables, since they are calculated per-row in the SELECT list.

 

You can always try wrapping the entire thing in "SELECT * FROM (....)" to trick mysql.