CODEWITHSUNDEEP
sqlsql-server
jomsk1e
jomsk1e

Reputation: 3625

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

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! :)

Upvotes: 0

Views: 755

Answers (1)

Dgan
Dgan

Reputation: 10275

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;

Upvotes: 1

Related Questions