CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jballard81
jballard81

Reputation: 135

How to order by column not in select clause?

Is there a method to work around not being able to sort by a column not in the select clause? I am using datename to grabe month and year, but i need it to be sorted by actual date not alphabetically.

select   datename(month, dbo.ITR.ITR_TransDate) as MonthDt, 
        DATENAME(year, dbo.itr.ITR_TransDate) as YearDt, COUNT(distinct dbo.ITR.ITR_Reason) AS WKCount, COUNT(dbo.itr.itr_reason) AS LineCt 
        from dbo.ITR
where   (ITR_EmployeeID IN (N'robra', N'lewer', N'petam', N'whike', N'mclch', N'ricju')) AND    (dbo.ITR.ITR_TransDate >= '1/1/13' AND dbo.itr.ITR_TransDate <= '1/31/14') AND 
    (dbo.itr.ITR_Reason LIKE 'WO%' OR dbo.itr.ITR_Reason LIKE 'RW%')
group by DATENAME(year, dbo.itr.ITR_TransDate), datename(month, dbo.ITR.ITR_TransDate), MONTH(dbo.itr.itr_transdate)
order by dbo.ITR.ITR_TransDate

the order by i have is failing b/c it isn't in the group by or an aggregate. i can't add it to either b/c there are many many dates that it would return.

Upvotes: 0

Views: 1017

Answers (2)

Hafthor
Hafthor

Reputation: 16916

try this

select   datename(month, dbo.ITR.ITR_TransDate) as MonthDt, 
    DATENAME(year, dbo.itr.ITR_TransDate) as YearDt, COUNT(distinct dbo.ITR.ITR_Reason) AS WKCount, COUNT(dbo.itr.itr_reason) AS LineCt 
    from dbo.ITR
where   (ITR_EmployeeID IN (N'robra', N'lewer', N'petam', N'whike', N'mclch', N'ricju')) AND    (dbo.ITR.ITR_TransDate >= '1/1/13' AND dbo.itr.ITR_TransDate <= '1/31/14') AND 
(dbo.itr.ITR_Reason LIKE 'WO%' OR dbo.itr.ITR_Reason LIKE 'RW%')
group by DATENAME(year, dbo.itr.ITR_TransDate), datename(month, dbo.ITR.ITR_TransDate), MONTH(dbo.itr.itr_transdate)
order by DATENAME(year, dbo.itr.ITR_TransDate), MONTH(dbo.itr.itr_transdate)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You can just use an aggregation function in the order by:

order by min(dbo.ITR.ITR_TransDate)

This should work for your case.

Upvotes: 3

Related Questions