How to set Query into variable in MySql

Question

I want to find the item names for the cheapest and most expensive items for each group. But I don't know why I have SQL error (1064) near " SET @maxi := (SELECT A.ItName FROM tblqtdtl B INNER JOIN tblitem A ON A.ItCode = B.ItCode GROUP BY A.ItCode HAVING MAX(B.Price))". maybe there is something wrong or missing from my sql code

This is what I expecting for

vd code	vd name	cheapest item	cheapest price	Most expensive item	Most Expensive Price
Vd001	DEF Textile	Polyester	2600	Polyester	2600
Vd003	MNO Textile	Polyester	3228	Silk	3950
Vd004	XYZ Textile	Polyester	2800	Silk	3480

And here's my code

SET @mini :=(SELECT A.ItName FROM tblqtdtl B INNER JOIN tblitem A ON A.ItCode = B.ItCode 
                    GROUP BY A.ItCode HAVING MIN(B.Price)
                )
SET @maxi := (SELECT A.ItName FROM tblqtdtl B INNER JOIN tblitem A ON A.ItCode = B.ItCode 
                    GROUP BY A.ItCode HAVING MAX(B.Price)
                 )

SELECT V.VdCode, V.VdName, @mini AS 'Cheapest Item', MIN(B.Price) AS 'Cheapest Price', @maxi AS 'Most Expensive Item'
,MAX(B.Price) AS 'Most Expensive Price'
 
 FROM tblqthdr C 
 INNER JOIN tblvendor V ON C.VdCode = V.VdCode
 INNER JOIN tblqtdtl B ON C.DocNo = B.DocNo
 INNER JOIN tblitem A ON B.ItCode = A.ItCode
 WHERE V.ActInd='Y'
 GROUP BY V.VdCode

This is the Input script

tblqdtl

INSERT INTO `tblqtdtl` (`DocNo`, `ItCode`, `Price`) VALUES
    ('QT001', 'It004', 38700.00),
    ('QT002', 'It002', 25600.00),
    ('QT003', 'It005', 21000.00),
    ('QT004', 'It004', 34000.00),
    ('QT005', 'It003', 32000.00),
    ('QT006', 'It002', 31780.00),
    ('QT007', 'It001', 28000.00),
    ('QT008', 'It002', 21800.00),
    ('QT009', 'It004', 32600.00),
    ('QT010', 'It002', 27500.00);

tblitem

INSERT INTO `tblitem` (`ItCode`, `ItName`, `ItCtCode`) VALUES
    ('Itestt001', 'Cotton', 'ALA'),
    ('It002', 'Polyester', 'SIN'),
    ('It003', 'Wool', 'ALA'),
    ('It004', 'Silk', 'ALA'),
    ('It005', 'Rayon', 'SIN');

tblqhdr

INSERT INTO `tblqthdr` (`DocNo`, `DocDt`, `VdCode`) VALUES
    ('QT001', '20180102', 'Vd003'),
    ('QT002', '20180203', 'Vd001'),
    ('QT003', '20180203', 'Vd002'),
    ('QT004', '20180207', 'Vd004'),
    ('QT005', '20180304', 'Vd003'),
    ('QT006', '20180401', 'Vd003'),
    ('QT007', '20180701', 'Vd005'),
    ('QT008', '20180902', 'Vd002'),
    ('QT009', '20180902', 'Vd005'),
    ('QT010', '20181203', 'Vd004');

tblvendor

INSERT INTO `tblvendor` (`VdCode`, `VdName`, `ActInd`) VALUES
    ('Vd001', 'ABC Textile', 'Y'),
    ('Vd002', 'DEF Textile', 'N'),
    ('Vd003', 'GHI Textile', 'Y'),
    ('Vd004', 'JKL Textile', 'Y'),
    ('Vd005', 'MNO Textile', 'N');

Answer 1

SELECT DISTINCT
       VdCode,
       VdName,
       FIRST_VALUE(ItName) OVER (PARTITION BY VdCode ORDER BY Price ASC) `Cheapest item`,
       MIN(Price) OVER (PARTITION BY VdCode) `Cheapest price`,
       FIRST_VALUE(ItName) OVER (PARTITION BY VdCode ORDER BY Price DESC) `Most expensive item`,
       MAX(Price) OVER (PARTITION BY VdCode) `Most Expensive Price`
FROM ( SELECT *
       FROM tblqthdr C 
       INNER JOIN tblvendor V USING (VdCode)
       INNER JOIN tblqtdtl B USING (DocNo)
       INNER JOIN tblitem A USING (ItCode)
       WHERE V.ActInd='Y' ) subquery

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=53cade7afe509dcde6f259e5c5ae3fa3