SQL Query for obtaining min and max sizes from product variants

 With Sizes_CTE (Product, DisplayOrder, Size)  
 AS (  
 select DISPLAYPRODUCTNUMBER Product, RETAILDISPLAYORDER DisplayOrder , NAME Size from ECORESPRODUCTMASTERDIMENSIONVALUE   
 inner join ECORESSIZE on ECORESSIZE.RecId = SIZE_  
 inner join ECOResProduct on EcoresProduct.RECID = SIZEPRODUCTMASTER)  
 select Z.Product, Concat(max(Z.MinSize), ' - ', max(Z.MaxSize)) SizeGroup from  
 (Select Product, Size MinSize, '' MaxSize from Sizes_CTE  
 where Sizes_CTE.DisplayOrder = (select min(DisplayOrder) from Sizes_CTE b where b.Product = Sizes_CTE.product )  
 union   
 Select Product, '' MinSize, Size MaxSize from Sizes_CTE  
 where Sizes_CTE.DisplayOrder = (select max(DisplayOrder) from Sizes_CTE b where b.Product = Sizes_CTE.product )) AS Z  
 group by Z.Product  
 order by Z.Product