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 (Pr…

Accepted Answer

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.

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

Solution

Problem