Help with query - 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 2005 Forums

Transact-SQL (2005)

Help with query

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-13 : 06:56:46

Table A [WiseTopic_Topics_Category]
- ID (int)(PK)

Table B [WiseTopic_Topics_Discussion]
- CategoryId (FK)
- Topic ID (PK)

I want to get the top 10 category records, plus for each row I need the count of "Topics" in table b.

Thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 07:08:02

select
top 10 *,
(select count(*) from WiseTopic_Topics_Discussion b where b.id=a.id)
from WiseTopic_Topics_Category a order by id

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-11-13 : 07:53:27

Cool, works great. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 08:01:04

select *, count(col) over (partition by id) from table

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 08:50:06

quote:
Originally posted by madhivanan

select top 10 *, count(col) over (partition by id) from table order by id

Madhivanan

Failing to plan is Planning to fail

missed top 10 part?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 01:11:04

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

select top 10 *, count(col) over (partition by id) from table order by id

Madhivanan

Failing to plan is Planning to fail

missed top 10 part?

Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources