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.
| Author |
Topic |
|
vinaynaran
Starting Member
9 Posts |
 Posted - 2011-07-19 : 09:05:38
|
| Dear all,I have table in SQL server called “accval” in that field called branch, accno, valEach branch have thousand of accounts. I want top 10 accounts by value in descending order from each branches.I had tried…select a.branch,a.accno, SUM(a.val)from E_AccountVal as awhere SUM(a.val) in (select top 10 sum(b.val) from E_AccountVal as b where b.branch = a.branch order by sum(b.val) desc)order by a.branch, SUM(a.val) descbut it’s give me error like…Msg 147, Level 15, State 1, Line 4An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.Please help me to build select query.Vinay |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2011-07-19 : 09:25:42
|
| You can use ROW_NUMBER for this. See the first example mentioned here: http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspxAlso, there is a major flaw in your code you've posted here. Even if it had worked, the results would be incorrect since you are trying to return any rows that have the same SUM as the ones from your inner query. Surely, a lot of accounts can add up to the same total but are not necessarily the ones you are looking for. A better approach would be to use a condition that is more selective, such as the primary key.OS |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
top n records from each group