MySQL: MIN() and MAX() against a string value

Question

I have an SQL SELECT statement joining 2 tables.
The main table contains misc information on a product and is joined to a second table of sizes.
The second table contains a list of non-numeric sizes stored as a string and is simply structured as follows...

SizeID = Primary Key
SizeName = String value of size (i.e. Small, Medium, Large)
SizeOrder = Integer value to sort the order of sizes (i.e. A SizeOrder of 5 would mean the Size was larger than a SizeOrder of 2)

I need the SELECT statement to return the MIN() and MAX() sizes from the table of sizes.
However, as the actual size is stored as a string, i need to run the MIN() and MAX() functions against the SizeOrder column, but return the value of the SizeName column.

My current attempt is as follows:

SELECT ProductReference, MIN(SizeOrder) AS MinSizeID, MAX(SizeOrder) AS MaxSizeID, 
(SELECT SizeName FROM Size WHERE SizeOrder = MinSizeID) AS MinSizeText, 
(SELECT SizeName FROM Size WHERE SizeOrder = MaxSizeID) AS MaxSizeText
FROM (Product INNER JOIN Size ON Products.SizeFK = StoneSize.SizeID) 
WHERE ID = 132
GROUP BY ProductReference;

This returns the error "Reference 'MinSizeID' not supported (reference to group function)"

Answer 1

MySQL is complaining about your using of the alias of the columns in the subselects, try

SELECT ProductReference, MIN(SizeOrder) AS MinSizeID, MAX(SizeOrder) AS MaxSizeID, 
(SELECT SizeName FROM Size WHERE SizeOrder = MIN(Products.SizeOrder)) AS MinSizeText, 
(SELECT SizeName FROM Size WHERE SizeOrder = MAX(Products.SizeOrder)) AS MaxSizeText
FROM (Products INNER JOIN Size ON Products.SizeFK = StoneSize.SizeID) 
WHERE ID = 132
GROUP BY ProductReference;

although I'm not sure if that will complain about selecting columns not in the group by.

Answer 2

You should do the whole query using the SizeOrder fields only from Size table, and then join the result with the Size table for the SizeName fields.