sql query

[Paul Boey]

Dear Sir,

Given the table structure:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Module_Tbl]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Module_Tbl]
GO

CREATE TABLE [dbo].[Module_Tbl] (
[ModuleID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ModuleDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ParentModuleID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

How to find a Parent Module having the most children?

[John Bell]

Hi

It always best to also post example data with your DDL to make things easier
for the replying poster, along with your expected results for that data.

Try

SELECT A.[ModuleID],A.[ModuleDesc], B.Cnt
FROM [dbo].[Module_Tbl] A
JOIN ( SELECT [ParentModuleID], COUNT(*) as Cnt
FROM [dbo].[Module_Tbl]
GROUP BY [ParentModuleID]
) B ON B.[ParentModuleID] = A.[ModuleID]
WHERE B.Cnt = ( SELECT Max(cnt) FROM
( SELECT [ParentModuleID], COUNT(*) as Cnt
FROM [dbo].[Module_Tbl]
GROUP BY [ParentModuleID] ) B )

John

"Paul Boey" <pa******@yahoo.com> wrote in message
news:9f**************************@posting.google.c om...

Dear Sir,

Given the table structure:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Module_Tbl]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Module_Tbl]
GO

CREATE TABLE [dbo].[Module_Tbl] (
[ModuleID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ModuleDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ParentModuleID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

How to find a Parent Module having the most children?

[David Portas]

SELECT parentmoduleid
FROM Module_Tbl
GROUP BY parentmoduleid
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM Module_Tbl
GROUP BY parentmoduleid)

--
David Portas
------------
Please reply only to the newsgroup
--