Order by on computed value

[prasadmokashi]

I am using Compute clause......and now I want to order the results based on the computed value.

I referred the documents, but it seems impossible because compute clause itself needs one order by clause.

So for example,

I have records in table1

User Acct
---------------------------
Prasad A
Prasad B
Prasad C
Nitin A
Nitin B
Girish A
Nachiket A
Nachiket B


if I run,
select User, Account
from Table1
order by User
compute count(Account) by User

I should get
Prasad A
Prasad B
Prasad C
----
3
Nitin A
Nitin B
----
2
Nachiket A
Nachiket B
----
2
Girish A
----
1


As you can see from above expected output I want users with maximum no of accounts come first in the list.

Do you know any way to do this ??

Thanks,
Prasad

 

[JenJohnson]

Prasad:

Try the following:


select User, Account,count(Account) as num
into #tmp
from Table1
group by User

select a.User, a.Account
from Table1 a, #tmp b
where a.User = b.User
and a.Account = b.Account
order by convert (varchar,num)+a.User desc
compute count(a.Account) by convert (varchar,num)+a.User

 

[prasadmokashi]

Thank you Jain.

I tried this, but still not getting the expected result.

Regards,
Prasad

 

[nikhilparchure]

try this I hope this works...
Select User, Account, convert(int , 0) as 'count'
into #First
from Table1
Order by User, Account

Select User , count(Account) 'count'
into #Second
from Table1
Group By count(Account)

Update #First
Set count = B.count
from #First A , #Second B
where A.User = B.User

Select *
From #First
Order count Desc , User Asc , Account Asc

 

[Massy]

okay try this

select username,
acct,
(select count(acct) from table1 b where b.username = a.username) as acct_cnt
from table1 a
order by acct_cnt desc

Tom.