SQL Server Select Distinct and Order By with CASE

Question

I've seen quite a few questions/forum posts regarding this scenario but I either don't understand the solutions or the solutions provided are too specific to that particular question and I don't know how to apply it to my situation. I have the following query:

SELECT DISTINCT d.*
FROM Data d
JOIN Customers c 
ON c.Customer_Name = d.Customer_Name
AND c.subMarket = d.subMarket
JOIN Sort s 
ON s.Market = c.Market
ORDER BY d.Customer_Name, d.Category, d.Tab, d.SubMarket,
CASE s.sortBy
WHEN 'Comp_Rank'
THEN d.Comp_Rank
WHEN 'Market_Rank'
THEN d.Market_Rank
ELSE d.Other_Rank
END

I used that exact query on my MySQL database and it worked perfectly. We recently switched over to a SQL Server database and now it doesn't work and I get the error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I've tried adding s.* to the SELECT (since s.sortBy is in the CASE) and that didn't change anything and I also tried listing out every single field in Data and Sort in SELECT and that resulted in the same exact error.

There actually aren't duplicates in Data, but when I do the joins it results in 4 exact duplicate rows for every single item and I don't know how to fix that so that's why I originally added the DISTINCT. I tried variations of LEFT JOINs, INNER JOINs, etc... and couldn't get a different result. Anyway, a solution to either issue would be fine but I'm assuming more information would be needed to figure out the JOIN duplicate issue.

Edit: I just realized that I mistakenly typed some of the fields in the ORDER BY (example, n.Category, n.Tab should have been d.Category, d.Tab). EVERYTHING in the ORDER BY is from the Data table which I've selected * from. As I said, I also tried listing out every field in the SELECT and that didn't help.

Answer 1

As the error suggests, when you use select distinct, you have to order by the expressions in the select clause. So, your case is an issue as well as all the columns not from d.

You can fix this by using group by instead, and including the columns that you want to sort by. Because the case includes a column from s, you need to include the case (or at least that column) in the group by:

SELECT d.*
FROM Data d JOIN
     Customers c 
     ON c.Customer_Name = d.Customer_Name AND
        c.subMarket = d.subMarket JOIN Sort s 
     ON s.Market = c.Market
GROUP BY "d.*",
         (CASE s.sortBy WHEN 'Comp_Rank' THEN d.Comp_Rank
                        WHEN 'Market_Rank' THEN d.Market_Rank
                        ELSE d.Other_Rank
         END)
ORDER BY d.Customer_Name, d.Category, d.Tab, d.SubMarket,
         (CASE s.sortBy WHEN 'Comp_Rank' THEN d.Comp_Rank
                        WHEN 'Market_Rank' THEN d.Market_Rank
                        ELSE d.Other_Rank
         END)

Note that "d.*" is in quotes. You need to list out all the columns in the group by.

Answer 2

Try this:

SELECT DISTINCT d.Customer_Name, d.Category, d.Tab, d.SubMarket,
  CASE s.sortBy
    WHEN 'Comp_Rank'
      THEN d.Comp_Rank
    WHEN 'Market_Rank'
      THEN d.Market_Rank
    ELSE
      d.Other_Rank
  END

FROM Data d

JOIN Customers c 
  ON c.Customer_Name = d.Customer_Name
 AND c.subMarket = d.subMarket

JOIN Sort s 
  ON s.Market = c.Market

ORDER BY d.Customer_Name, d.Category, d.Tab, d.SubMarket,
  CASE s.sortBy
    WHEN 'Comp_Rank'
      THEN d.Comp_Rank
    WHEN 'Market_Rank'
      THEN d.Market_Rank
    ELSE
      d.Other_Rank
  END

Answer 3

Just to follow up on this, you just need to add the case to your SELECT with an AS. Then include that reference in the ORDER BY list.

SELECT DISTINCT d.*, case when ... then ... else ... end AS MyCase
...
ORDER BY d.Customer_Name, d.Category, d.Tab, d.SubMarket, MyCase

This answer is similar to Pang's, but I find not having to include the case statement in both the top and bottom better as errors could arise if you modified one and not the other.