Nested Select query

[Poonam Gandash]

"select * from (select username,designation, count(*) as points from (select username,designation from messages union all select username,designation from replies) group by username,designation) order by points desc" this query works fine with MS Access but not with SQL Server. Through this query I am listing most active members of forum by counting no. of message and replies. Members are 2 types- Students & Professionals PLZ.... Help..

[Krish KP]

hope you are getting error message at group by clause if so, try this select * from (select username,designation, count(*) as points from (select username,designation from messages union all select username,designation from replies )t1 group by username,designation )t2 order by points desc

Regards KP

[andyharman]

Try:

select * from (
  select username, designation, count(*) as points
  from (
    select username, designation from messages
    union all
    select username, designation from replies
  ) as A
  group by username, designation
) as B
order by points desc

You need to provide table aliases. I'm not sure where menber-types fit into this. Regards Andy

[vimal_yet]

What is the exact purpose of this Query ???

If U Get Errors U Will Learn If U Don't Get Errors U Have Learnt

[Poonam Gandash]

Thanks a lot it worked for me.. I rated u 5 This is 4 u :rose: :)