Database

SELECT *
FROM nodes2
CONNECT BY PRIOR Id = RefParent
START WITH RefParent IS NULL
ORDER SIBLINGS BY Id

WITH records AS
(
    SELECT
        Id,
        RefParent,
        CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY Id)) AS thePath
    FROM  nodes2
    WHERE RefParent IS NULL
        UNION ALL
    SELECT
        n.Id,
        n.RefParent,
        r.thePath + '.' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY n.Id)) AS thePath
    FROM records r
    JOIN nodes2 n ON n.RefParent = r.Id
)
SELECT *
FROM records
ORDER BY 
    thePath

/****** Object:  StoredProcedure [dbo].[sp_Traverse_Tree_Special]    Script Date: 02/16/2010 22:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 


ALTER  PROCEDURE [dbo].[sp_Traverse_Tree_Special] @curr int
with recompile
AS
set nocount on
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes]'))
  drop table [#Nodes]
  if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes_FINAL]'))
  drop table [#Nodes_FINAL]

  if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#SemiFinal]'))
  drop table [#SemiFinal]
  
  
  if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Surt1]'))
  drop table [#Surt1]
  
    if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Children]'))
  drop table [#Children]
  
  
Create table [#Nodes]
(id int IDENTITY(0,1),
 NodeID int,
 parent int,
 [Description] varchar(128),
 refParent int
)
 
Create table [#Nodes_FINAL]
(id int IDENTITY(0,1),
 id2 int ,
 NodeID int,
 parent int,
 [Description] varchar(128),
 refParent int
)
INSERT INTO #Nodes
   (NodeID, Parent, [Description])
select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description]
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of')
where n.curr =  @curr
union
select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject'
inner join tbl_modl m on m.id = n.modl
where n.curr =  @curr
 
 select distinct  nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent 
into #nodes2 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID	-- look up the reference id of the parent
order by  refParent,nP.Description

WITH CTENodes AS
(
SELECT
     ID,
     NODEID,
     PARENT,
     DESCRIPTION,
     REFPARENT,
     CAST(ROW_NUMBER() OVER(ORDER BY id  ) AS VARCHAR(MAX)) NodePath
  FROM #nodes2
  WHERE REFPARENT is null
UNION ALL
  SELECT
     c.ID,
     c.NODEID,
     c.PARENT,
     c.DESCRIPTION,
     c.REFPARENT,
     NodePath + '.' + CAST(ROW_NUMBER() OVER(ORDER BY C.id ) AS VARCHAR(MAX)) NodePath
  FROM CTENodes AS P
    JOIN #nodes2 AS C
      ON C.REFPARENT = P.id
)
SELECT  ID,NODEID, PARENT, DESCRIPTION, REFPARENT
into #SemiFinal
FROM CTENodes
ORDER BY NodePath,ID,REFPARENT 

--These are all Records that inherit from the Root 
select distinct s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Surt1 from #SemiFinal s1
inner join #SemiFinal s2
on s1.id = s2.id 
where s1.RefParent  = 0
and s1.id != s1.Refparent
and s1.Parent  in  (select Nodeid from #semifinal where Parent is null and ID = 0)

--Find Records that are Children
select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Children from  #SemiFinal s1
where Refparent <> 0

--Get the Record that does not have Children
--select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent  from #Surt1 s1 
--where s1.id  not in (select refparent from #Children)

--delete those that have Children from the #surf1 table 
delete #Surt1
from #Surt1 s1 
where s1.id    in (select refparent from #Children)


--Then Union the Results the way you want them 
--First Union the Root
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #SemiFinal 
where Refparent is null 
 
--Follow with the Children 
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID  , nodeid  , parent, [description], RefParent from #Surt1
 
--Follow with the Normal
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID, nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is not null 
and ID not in (select ID from #Surt1)



select distinct ID, nodeid , parent, [description] from #Nodes_FINAL
order by nP.id , nodeid

SELECT  *
FROM    Table
HAVING  Count(name) > 1

Yulianto. wrote:

sql server express for LAN

Yulianto. wrote:

Isn't there an easy way to do this?

SELECT Code, Defects from
(SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code)
ORDER BY Defects;

SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);

ORDER BY Defects

ORDER BY SUM(QualityData.NrOfDefects);