So I have to find the half value of row from this ‘price’ column and I have to name that half price condition as PromotionPrice, secondly I need to find the PromotionPrice with highest value using rank;

Values

INSERT IGNORE INTO brand_item(name,price,shoe_name) 
    VALUES
    ("Nikey",300,"ZingZang"),
    ("Nikey",140,"Limbroz"),
    ("zing",720,"Canterous"),
    ("zing",500,"Heras"),
    ("zing",120,"Kazolvo"),
    ("Lobroso",450,"At"),
    ("Lobroso",150,"Hipsirus");

My solution

SELECT name,shoe_name,price,
    (price/2) AS PromotionPrice
    FROM (
        SELECT 
            name,shoe_name,price,PromotionPrice,
            RANK() OVER (PARTITION BY price ORDER BY PromotionPrice DESC)
            AS RNK
        FROM interviewqs.brand_item
) tmp
WHERE name = "Lobroso" AND RNK = 1;

But my console throws this error; Unknown column PromotionPrice in the field list. I know there’s a correct syntax to achieve what I desired for but I have done several googling yet no answer.