ORDER BY items must appear . . .

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ORDER BY items must appear . . .

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-11-10 : 13:37:17

I am getting the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

I have a query that is working but I am trying to add "minutes used" which is a combination of several records that can have minutes in them. if I dont dont add "sum(distinct tt.col_10)/60," with just tt.col_10 I can get several rows. I would just like to do 1 row with a total...

SELECT DISTINCT t.serve_no,
cc.Costcentre,
t.acc_header,
t.se AS network_access,
sum(distinct tt.col_10)/60,
trans_total AS monthlytotal,
Datepart(MONTH, ah.bill_date) AS MONTH,
Datepart(YEAR, ah.bill_date) AS YEAR
FROM transactions t
INNER JOIN accountheader ah ON ah.acc_header = t.acc_header
INNER JOIN CostCentres cc ON cc.serve_no = t.serve_no
inner join services s on s.serve_no = t.serve_no
inner join itemisedCharges tt on tt.acc_header= t.acc_header
WHERE line_status = 'Active'
AND serv_class = 'WIRELESS'
and Datepart(MONTH, ah.bill_date) = '12'
and Datepart(YEAR, ah.bill_date) = '2008'
and cc.Costcentre='89300000'
and tt.Charge_cat='airtime'
ORDER BY cc.Costcentre , t.serve_no, tt.col_10

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 14:11:46

And you think if you're order-by-problem is solved then you get only one row with a total?

No, you're never too old to Yak'n'Roll if you're too young to die.