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

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,
This works fine

SQL

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

SQL

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 25-Oct-12 18:18pm

pranathis012

Updated 15-Mar-19 10:38am

v3

Add a Solution

3 solutions

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

Posted 15-Mar-19 10:38am

Member 10229842

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kuthuparakkal · Accepted Answer · 2012-10-25T18:26:00

Solution 2

Remove DISTINCT and add GROUP BY clause

Posted 25-Oct-12 18:26pm

Kuthuparakkal

Comments

[no name] 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.

Rajesh Kariyavula · Accepted Answer · 2012-10-25T18:29:00

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.

Posted 25-Oct-12 18:29pm

Rajesh Kariyavula

Comments

[no name] 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

[no name] 26-Oct-12 1:15am

k fine thank u

[no name] 26-Oct-12 1:15am

it's help me alot

Rajesh Kariyavula 26-Oct-12 1:34am

Happy to hear that it helped you.