Click here to Skip to main content
14,207,126 members
Rate this:
 
Please Sign up or sign in to vote.
Hi,
This works fine
SELECT DISTINCT SED.MCSED_SED_Short_Description, SCH.Doc_SNo,
 FROM @Schedule_Status SCH  , DMS_M_Company_Schedule_Element_Details SED
 WHERE SED.MCSED_SED_Code =123
 ORDER BY   SED.MCSED_SED_Short_Description, SCH.Doc_SNo, SCH.Doc_No


But when i introduce case expression in order by clause it shows the error
SELECT DISTINCT SED.MCSED_SED_Short_Description, SCH.Doc_SNo,
 FROM @Schedule_Status SCH  , DMS_M_Company_Schedule_Element_Details SED
 WHERE SED.MCSED_SED_Code =123
 ORDER BY   CASE SED.MCSED_SED_Short_Description WHEN 'VD' THEN 1 ELSE 2 END,
                   SCH.Doc_SNo, SCH.Doc_No


it shows the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
Posted
Updated 15-Mar-19 10:38am
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Remove DISTINCT and add GROUP BY clause
   
Comments
pranathis012 26-Oct-12 0:28am
   
sry this SP used in some other project if i modified like that it's effect on existing project. why i introduce that DISTINCT is to remove duplicate values.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

To fix the issue just add the field SCH.Doc_No to your select clause.

To know more about this issue read the below article :

SQL Distinct & Order by Issue[^]

Hope this helps.
   
Comments
pranathis012 26-Oct-12 0:46am
   
K i will try using Group clause. But my small soubt is , what are the fields are called in select query that total columns has to be used in Group caluse ... is it right.
for ex in my select query i called 20 columns now in my group clause also i want to call total 20 columns. please help me in this..
Rajesh Kariyavula 26-Oct-12 0:51am
   
Yes for grouping you have to select the fields, all fields mentioned in Group By clause should be there in select clause also
pranathis012 26-Oct-12 1:15am
   
k fine thank u
pranathis012 26-Oct-12 1:15am
   
it's help me alot
Rajesh Kariyavula 26-Oct-12 1:34am
   
Happy to hear that it helped you.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

SELECT DISTINCT SED.MCSED_SED_Short_Description, SCH.Doc_SNo,
     ord = CASE SED.MCSED_SED_Short_Description WHEN 'VD' THEN 1 ELSE 2 END,
     SCH.Doc_No

 FROM @Schedule_Status SCH  , DMS_M_Company_Schedule_Element_Details SED
 WHERE SED.MCSED_SED_Code =123
 ORDER BY   CASE SED.MCSED_SED_Short_Description WHEN 'VD' THEN 1 ELSE 2 END,
                   SCH.Doc_SNo, SCH.Doc_No
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190612.1 | Last Updated 15 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100