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)"