title: SQL Server Select Distinct and Order By with CASE
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
  </code></pre>
  
  <p><strong>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.</strong>  </p>
  
  <p>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. </p>
  
  <p>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.</p>