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_Gene ral_CP1_CI_AS NOT
NULL ,
[ModuleDesc] [varchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ParentModuleID] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

How to find a Parent Module having the most children?

[John Bell]

Re: sql query

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" <paulboey@yahoo .com> wrote in message
news:9f7c5d5c.0 401162058.1d278 6b5@posting.goo gle.com...[color=blue]
> 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_Gene ral_CP1_CI_AS NOT
> NULL ,
> [ModuleDesc] [varchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
> NULL ,
> [ParentModuleID] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> How to find a Parent Module having the most children?[/color]

[David Portas]

Re: sql query

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
--