CODEWITHSUNDEEP
sqlsql-order-bycasedistinct
Abhinav Parashar
Abhinav Parashar

Reputation: 649

SQL server Select Distinct with Order By

I am not very known to DB query but getting error

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

with below query. I searched on google and found that order by need to group if using Distinct but still not able to get it. Can anyone help me out.

SELECT DISTINCT 
       P1.*
FROM T_PRD P1
     LEFT JOIN T_PRD P2 ON P1.baseprdid = P2.prdid
     INNER JOIN T_PRD_NM_VENDOR prdNmVendor ON P1.prdId = prdNmVendor.prdId
     INNER JOIN T_VENDOR_NM vendorNM ON prdNmVendor.vendorNMId = vendorNM.vendorNMId
     INNER JOIN T_NM nm ON vendorNM.NMId = nm.NMId
     INNER JOIN T_PRD_VENDOR prdVendor ON prdVendor.PRDId = P1.PRDId
     INNER JOIN T_VENDOR vendor ON prdVendor.vendorId = vendor.vendorId
     INNER JOIN T_CSTMR_PRD_REF custPrd ON custPrd.ProductId = P1.PRDId
     INNER JOIN T_CSTMR cstmr ON custPrd.ChennelCstrId = cstmr.cstmrid
WHERE 1 = 1
      AND P1.Lifecycle = 2
      AND P1.AutoCreated = 0
      AND vendor.vendorId = 1
      AND P1.VendorEnfId = 1
      AND cstmr.cstmrid = 2008
ORDER BY CASE
             WHEN P1.BASEPRDID = 0
             THEN P1.PRDNAME
             ELSE P2.PRDNAME
         END ASC, 
         BASEPRDID;

Upvotes: 0

Views: 63

Answers (1)

Serg
Serg

Reputation: 22811

Your ORDER BY depends on expression which is missing from SELECT DISTINCT list . Add it

 SELECT DISTINCT  P1.*
        ,CASE
             WHEN P1.BASEPRDID = 0
             THEN P1.PRDNAME
             ELSE P2.PRDNAME
         END col

Upvotes: 1

Related Questions