SELECT DISTINCT error Msg 145 level 15

[vba317]

Hello,
I have changed a query from select to select distinct and I keep on getting an error that I can't seem to fix. The error states: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified. I have added all the categories from the select statement into the order by statement but I still get the error. Any help is appreciated.

SELECT DISTINCT
ar.UCI
,(case when ar.transagebucket < 1 then '0_30'
		when ar.transagebucket = 1 then '31_60'
		when ar.transagebucket = 2 then '61_90'
		when ar.transagebucket = 3 then '91_120'
		when ar.transagebucket = 4 then '121_150'
		when ar.transagebucket = 5 then '151_180'
		else 'Over180' end) as AgeBucket
,ar.PatName as PatientName
,ar.AcctNu as AccountNumber
,Convert (varchar(10),ar.dos,101) as Svc_Date
,ar.cptdisplay
,(ins.insdesc+ ' - ' +ins.insmne) as Insurance
---,Sum(ar.chgamt) as ChgAmt
,(ar.chgamt) as ChgAmt
---,ISNULL(Sum(pmt.pmtamt),0) as PmtAmt
---,Sum(ar.curbal) as CurBal
,(ar.curbal) as CurBal
FROM rpt_dat_ARDetail ar
	INNER JOIN rpt_dic_Ins ins ON ar.insmne = ins.insmne AND ar.clntid = ins.clntid
---    LEFT JOIN rpt_dat_PmtDetail pmt ON ar.clntid = pmt.clntid AND ar.AcctNu = pmt.AcctNu And ar.dos = pmt.dos
WHERE ar.reportmonth ='5/1/2013' and ar.clntid =69 
---GROUP BY ar.uci,ar.transagebucket,ar.PatName,ar.AcctNu,ar.dos,ar.cptdisplay,ins.insdesc,ins.insmne,ar.curbal,pmt.pmtamt
GROUP BY ar.UCI,ar.transagebucket,ar.PatName,ar.AcctNu,ar.dos,ar.cptdisplay,ins.insdesc,ins.insmne,ar.chgamt,ar.curbal
---ORDER BY ar.transagebucket ASC,ar.PatName ASC,ar.AcctNu,ar.dos ASC,ar.cptdisplay,ins.insdesc,ins.insmne,pmt.pmtamt;
ORDER BY ar.UCI,ar.transagebucket ASC,ar.PatName ASC,ar.AcctNu,ar.dos ASC,ar.cptdisplay,ins.insdesc,ins.insmne,ar.chgamt,ar.curbal;

 

[simian336]

What happens if you remove the order by?

Simi

 

[vba317]

I don't get the error but the order of the results is wrong.

 

[philhege]

It looks like you're missing Pmt.PmtAmt. It' in the commented clause, but not the active one.

-----------
With business clients like mine, you'd be better off herding cats.

 

[gmmastros]

When you use distinct in your query, you can only order by the columns that are returned. I haven't looked at all of your columns, but the one that jumps out at me is ar.transagebucket. You are not returning this column and you are using distinct, so you cannot use it in the order by.

You can reference an aliased column name in your order by, so you could do this:

Order By ar.UCI, [!]AgeBucket[/!], etc....

This, of course, won't give you the ordering you want either because AgeBucket is a string, so it will order like a string. Your ordering would be something like 0_30, 121_150, 151_180, 31_60 etc....

There is, of course, a way to get the ordering you want.

Select  UCI, AgeBucket, PatientName, AccountNumber, Svc_Date, cptDisplay, Insurance, ChgAmt, CurBal
From    (
        SELECT DISTINCT
               ar.UCI
               ,(case when ar.transagebucket < 1 then '0_30'
		               when ar.transagebucket = 1 then '31_60'
		               when ar.transagebucket = 2 then '61_90'
		               when ar.transagebucket = 3 then '91_120'
		               when ar.transagebucket = 4 then '121_150'
		               when ar.transagebucket = 5 then '151_180'
		               else 'Over180' end) as AgeBucket
               [!],ar.transagebucket [/!]
               ,ar.PatName as PatientName
               ,ar.AcctNu as AccountNumber
               ,Convert (varchar(10),ar.dos,101) as Svc_Date
               ,ar.cptdisplay
               ,(ins.insdesc+ ' - ' +ins.insmne) as Insurance
               ---,Sum(ar.chgamt) as ChgAmt
               ,(ar.chgamt) as ChgAmt
               ---,ISNULL(Sum(pmt.pmtamt),0) as PmtAmt
               ---,Sum(ar.curbal) as CurBal
               ,(ar.curbal) as CurBal
               FROM rpt_dat_ARDetail ar
	               INNER JOIN rpt_dic_Ins ins ON ar.insmne = ins.insmne AND ar.clntid = ins.clntid
               ---    LEFT JOIN rpt_dat_PmtDetail pmt ON ar.clntid = pmt.clntid AND ar.AcctNu = pmt.AcctNu And ar.dos = pmt.dos
               WHERE ar.reportmonth ='5/1/2013' and ar.clntid =69 
               ---GROUP BY ar.uci,ar.transagebucket,ar.PatName,ar.AcctNu,ar.dos,ar.cptdisplay,ins.insdesc,ins.insmne,ar.curbal,pmt.pmtamt
               GROUP BY ar.UCI,ar.transagebucket,ar.PatName,ar.AcctNu,ar.dos,ar.cptdisplay,ins.insdesc,ins.insmne,ar.chgamt,ar.curbal
        ) As RawData
---ORDER BY ar.transagebucket ASC,ar.PatName ASC,ar.AcctNu,ar.dos ASC,ar.cptdisplay,ins.insdesc,ins.insmne,pmt.pmtamt;
ORDER BY UCI,[!]transagebucket [/!]ASC,PatientName ASC, AccountNumber,Svc_Date ASC,cptdisplay,Insurance,chgamt,curbal;

This technique is called a derived table. You could have gotten the same results by using a common table expression.

A couple things to note about this technique. Your original query is wrapped in parenthesis. You need to create an alias for the original query. In this case, I named it "RawData". Also note that you can only reference columns returned by the derived table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom