How to order by clause on distinct select stament condition value This is my query,

SELECT DISTINCT 
    BASE_Product.ProductId, 
    (CASE WHEN (BASE_Art.ArtId IS NOT NULL AND BASE_Heel.HeelId IS NULL) 
          THEN BASE_Art.ArtNo 
          ELSE (CASE WHEN (BASE_Art.ArtId IS NULL AND BASE_Heel.HeelId IS NOT NULL) 
                     THEN BASE_Heel.HeelNo 
                     ELSE (BASE_Art.ArtNo + '-' + BASE_Heel.HeelNo) END)END) AS ArtHeelNo, 
    BASE_InvoiceLine.Price
FROM 
    BASE_Product 
INNER JOIN BASE_ProductVariation 
    ON BASE_Product.ProductId = BASE_ProductVariation.ProductId 
INNER JOIN BASE_InvoiceLine 
    ON BASE_ProductVariation.ProductVariationId = BASE_InvoiceLine.ProductVariationId 
LEFT OUTER JOIN BASE_Art 
    ON BASE_Product.ArtId = BASE_Art.ArtId 
LEFT OUTER JOIN BASE_Heel 
    ON BASE_Product.HeelId = BASE_Heel.HeelId
WHERE 
    (BASE_InvoiceLine.InvoiceId = 2)


Once I execute the above query I'm getting the result like below screen shot.



This above result ordered by ProductId wise I want to get this order by HeelNo wise. 

Issue is I can't order by HeelNo wise because of order by clause on distinct select and case.. 

I want the result should be,



How can I do this..
Since ORDER BY is executed after SELECT you can use the alias that you've defined in your SELECT clause.

ORDER BY ArtHeelNo

Another (less recommended) option would be to just order by the number of the column

ORDER BY 2


It's also possible to just repeat the statements in your ORDER BY, this is less recommended since if your statement would change, you have to change it twice. It's also a lot messier to read.

`ORDER BY (CASE WHEN (BASE_Art.ArtId IS NOT NULL AND BASE_Heel.HeelId IS NULL) THEN BASE_Art.ArtNo                              ELSE (CASE WHEN (BASE_Art.ArtId IS NULL AND BASE_Heel.HeelId IS NOT NULL)                               THEN BASE_Heel.HeelNo ELSE (BASE_Art.ArtNo + '-' + BASE_Heel.HeelNo) END)END)`