0

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.

enter image description here

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,

enter image description here

How can I do this..

1

2 Answers 2

4

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)`
2
  • 1
    I wouldn’t repeat the expression, it’s messier, and it means if the expression ever changes, you need to remember to change it in both places. The expression has an alias, so why not just ORDER BY ArtHeelNo? Commented Aug 2, 2019 at 11:37
  • @AaronBertrand You're right, edited my answer. Commented Aug 2, 2019 at 12:44
3

As Yannick has suggested you can use the ordinal order, or you can use your query as a sub-query and use the column name, but you can use too the alias of CASE statement.

Given the next example:

CREATE TABLE test (ID int IDENTITY PRIMARY KEY, Val1 varchar(100));

INSERT INTO test VALUES
('BB'),('FF'),('AA'),('CC'),('DD');

Using the alias:

SELECT
    ID, CASE WHEN Val1='1' THEN Val1 ELSE Val1 END AS HeelNo
FROM
    test
ORDER BY
    HeelNo;

As a sub-query:

SELECT ID, Val1
FROM (SELECT
          ID, Val1
      FROM
          test) t1
ORDER BY
    Val1;

Using the ordinal:

SELECT
    ID, Val1
FROM
    test
ORDER BY
    2;

Both methods return the same result:

ID | Val1
-: | :---
 3 | AA  
 1 | BB  
 4 | CC  
 5 | DD  
 2 | FF  

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=fef1ee61098f226cceb37ac2a3369b1f

And as you can see in the fiddle, both methods show the same execution plan.

3
  • 1
    Using the ordinal number is brittle. Commented Aug 2, 2019 at 11:35
  • You don't need subquery, you can use the alias defined in the SELECT list. Commented Aug 2, 2019 at 13:29
  • @ypercubeᵀᴹ you're right
    – McNets
    Commented Aug 2, 2019 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.