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?