Why is this create an error - SQL Server Forums

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

Transact-SQL (2000)

Why is this create an error

Author

Topic

ronnyr
Starting Member

21 Posts

Posted - 2006-10-03 : 14:28:07

I got the error when I put my ORDER BY after GROUP BY statement, why is that?

Thank in advance.

Ron.

“Know where to find the information and how to use it - That's the secret of success”

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-03 : 14:31:56

show us the query.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

ronnyr
Starting Member

21 Posts

Posted - 2006-10-03 : 14:47:23

What I am trying to get here is the account with the last invoice date only, instead of showing the same account multiple times. So, all account should only show once with the last invoice date.

=======================================================
SELECT AccountID, AccountName, InvoiceDT, SUM(TotalVolume) as TotalVolume, SUM(TotalOrder) as TotalOrder FROM
(
SELECT
a.AccountID,
a.AccountName,
o.InvoiceDT,
TotalVolume = SUM(o.OrderFee),
TotalOrder = COUNT(o.OrderID)
FROM
Account a
JOIN oms.[Order] o ON o.AccountID = a.AccountID
WHERE
o.AccountID NOT IN (SELECT DISTINCT AccountID FROM [Order]
WHERE InvoiceDT IS NOT NULL AND
(InvoiceDT > @PeriodToView)
) AND
o.InvoiceDT IS NOT NULL AND
(o.InvoiceDT BETWEEN @PeriodToView - 90 AND @PeriodToView)
GROUP BY a.AccountID, a.AccountName, o.InvoiceDT
ORDER BY o.InvoiceDT DESC
) as tmp
GROUP BY AccountID, AccountName, InvoiceDT

===============================================================

Thx.

Ron.

“Know where to find the information and how to use it - That's the secret of success”

ronnyr
Starting Member

21 Posts

Posted - 2006-10-03 : 18:04:39

I got the problem solved. You cannot use ORDER BY in inline function without using TOP or FOR.

Hope this helps...

Ron.

“Know where to find the information and how to use it - That's the secret of success”

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources