ORDER BY items must appear in the select list if SELECT DISTINCT is specified using INNER JOIN

Question

I am having problem with my query, I read some suggestions regarding this, but with no luck.

I have 3 tables, Phase, Stage and Schedule.

Schedule columns:

ID, StageId, PhaseId, Duration, ScheduleType

Stage columns:

StageId, StageName

Phase columns:

PhaseId, PhaseName

And here's what I'm trying to get:

DECLARE @actual_startdate date = '2014-01-10'
SELECT 
    DISTINCT (C.PhaseName), 
    A.Duration, 
    @actual_startdate StartDate, 
    DATEADD(dd, A.Duration, @actual_startdate) EndDate 
FROM Schedule A
INNER JOIN Stages B
ON A.StageId = B.StageId
INNER JOIN Phase C
ON A.PhaseId = C.PhaseId
WHERE A.Schedule = '2' and B.Stage = '0.25'
ORDERBY B.StageId DESC;

And I'm getting this error:

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

I tried other suggestions as for my research to use GROUP BY clause instead of DISTINCT but still with no luck.

I tried this:

DECLARE @actual_startdate date = '2014-01-10'
SELECT 
    C.PhaseName, 
    A.Duration, 
    @actual_startdate StartDate, 
    DATEADD(dd, A.Duration, @actual_startdate) EndDate 
FROM Schedule A
INNER JOIN Stages B
ON A.StageId = B.StageId
INNER JOIN Phase C
ON A.PhaseId = C.PhaseId
WHERE A.Schedule = '2' and B.Stage = '0.25'
GROUP BY C.PhaseName
order BY B.StageId DESC;

And got new error:

Column 'Schedule.Duration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Hope someone can help me. Thanks! :)

Dgan · Accepted Answer · 2014-10-31T07:50:29.633

1

You need to Add A.Duration in Group By Clause

DECLARE @actual_startdate date = '2014-01-10'
SELECT 
    C.PhaseName, 
    A.Duration, 
    @actual_startdate StartDate, 
    DATEADD(dd, A.Duration, @actual_startdate) EndDate 
FROM Schedule A
INNER JOIN Stages B
ON A.StageId = B.StageId
INNER JOIN Phase C
ON A.PhaseId = C.PhaseId
WHERE A.Schedule = '2' and B.Stage = '0.25'
GROUP BY C.PhaseName,A.Duration,B.StageId 
order BY B.StageId DESC;

edited Oct 31 '14 at 07:50

answered Oct 31 '14 at 07:44

Dgan

10,077
1
29
51

I also tried this, but got this error 'Column "Stages.StageId" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.' – jomsk1e Oct 31 '14 at 07:48
fetl so very stupid about that! LOL Thanks a lot!! :) 6 minutes to accept your answer XD – jomsk1e Oct 31 '14 at 07:52

ORDER BY items must appear in the select list if SELECT DISTINCT is specified using INNER JOIN

1 Answers1