Sql Select using CTE to order recursive data
Please, I want a solution to this problem with CTE in SQL Server
Example for a situation
Equation 0 = 0.25*Equation 1
Equation 1 = Equation 2 + Equation 3 + 0.5*Equation 5
Equation 2 = 15 + 40
Equation 3 = Equation 6 + Equation 7
Equation 4 = 10
Equation 5 = 10 + Equation 4
Equation 6 = 10 +5
Equation 7 = Equation 5 + Equation 2
Structure of tables
Elements table
ID | Name
-------|--------------
0 | Equation 0
1 | Equation 1
2 | Equation 2
3 | Equation 3
4 | Equation 4
5 | Equation 5
6 | Equation 6
7 | Equation 7
---------------------
the table contains all the elements of each equation
Equation table
FK | Item | Type
-------|-----------|------------------
0 | 0.25 | constant
0 | * | Operator
0 | 1 | Element
1 | 2 | Element
1 | + | Operator
1 | 3 | Element
1 | + | Operator
1 | 0.5 | constant
1 | * | Operator
1 | 5 | Element
2 | 15 | constant
2 | + | Operator
2 | 40 | constant
… | |
… | |
… etc | |
------------------------------------
if the type is an element it means that it is an element
is there any result of sql expression in correct order that I should use to calculate these equations one by one without using recursive functions as it is limited in SQL the alternative is to calculate the last equation without any requirements and then calculate the upper ones, so how, when I need an equation, I find it calculated without recursion of equations
I need a select sql statement to create the following order
Equation 2
Equation 6
Equation 4
Equation 5
Equation 7
Equation 3
Equation 1
Equation 0
i ordered them by eye because this is a simple example if there is any select statement to do this or the user has to order them manually.
-
... Update
1.with fully working test case
Script for creating tables
/****** Object: Table [dbo].[Element] Script Date: 26/03/2017 11:10:10 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Element](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Element] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Equation] Script Date: 26/03/2017 11:10:10 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Equation](
[fk] [int] NOT NULL,
[Item] [nvarchar](50) NOT NULL,
[Type] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Element] ([Id], [Name]) VALUES (0, N'Equation 0')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (1, N'Equation 1')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (2, N'Equation 2')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (3, N'Equation 3')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (4, N'Equation 4')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (5, N'Equation 5')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (6, N'Equation 6')
INSERT [dbo].[Element] ([Id], [Name]) VALUES (7, N'Equation 7')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (0, N'0.25', N'constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (0, N'*', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (0, N'1', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'2', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'3', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'0.5', N'constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'*', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (1, N'5', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (2, N'15', N'constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (2, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (2, N'40', N'constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (3, N'6', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (3, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (3, N'7', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (4, N'10', N'Constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (5, N'10', N'Constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (5, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (5, N'4', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (6, N'10', N'Constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (6, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (6, N'5', N'Constant')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (7, N'5', N'Element')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (7, N'+', N'Operator')
INSERT [dbo].[Equation] ([fk], [Item], [Type]) VALUES (7, N'2', N'Element')
- *
@ Gordon Linoff
- ... Thanks a lot for your help, but I ran into some errors using the provided Script which I cannot solve since I was using CTE for the first time
The second Script gives me some errors that I cannot solve
the first Script gives me errors and I cannot solve
+3
source to share
2 answers
You can try it like this:
WITH Related AS
(
SELECT *
FROM Equation AS eq
LEFT JOIN Element AS e ON eq.[Type]='Element' AND eq.Item=CAST(e.Id AS VARCHAR(10))
WHERE eq.[Type]='Element'
)
,Dependecies AS
(
SELECT e.*
,ISNULL(r.Name,'') AS DepName
FROM Element AS e
LEFT JOIN Related AS r ON e.Id=r.fk
)
,recCTE AS
(
SELECT 1 AS lvl,d.Id,d.Name,d.DepName
FROM Dependecies AS d
WHERE d.Name NOT IN(SELECT x.DepName FROM Dependecies AS x)
UNION ALL
SELECT r.lvl+1,d.Id,d.Name,d.DepName
FROM recCTE AS r
INNER JOIN Dependecies AS d ON r.DepName=d.Name
)
,Ranked AS
(
SELECT Name
,DENSE_RANK() OVER(ORDER BY CASE WHEN DepName='' THEN 1000 ELSE lvl END DESC) AS Rnk
FROM recCTE
)
SELECT Name,MIN(Rnk) AS Rnk
FROM Ranked
GROUP BY Name
ORDER BY Min(Rnk)
Result
Equation 2 1
Equation 4 1
Equation 6 1
Equation 5 2
Equation 7 3
Equation 3 4
Equation 1 5
Equation 0 6
Description
There is a CTE list:
- The first CTE will concatenate the Elements to Equation strings, where type
Element
. - Second list of all elements with their dependencies
- The third CTE is a recursive CTE starting with an element without any dependencies, running along the dependency path
- The following CTE is used
DENSE_RANK() OVER()
to get ordered calls
Ultimate SELECT
returns every item and the earliest time needed.
+3
source to share
Oh it would be swelling if we could express it like:
with cte as (
select e.fk, 1 as lev
from equation e
group by e.fk
having sum(case when type = 'Element' then 1 else 0 end) = 0
union all
select e.fk, max(cte.lev) + 1
from equation e left join
cte
on e.fk = cte.fk
group by e.fk
having count(*) = count(cte.fk)
)
But this is impossible. So we have to think about string manipulation (I think). This results in putting dependencies on the string and chopping items out of the string multiple times. If I have this right:
with eq as (
select e.fk,
stuff( (select ',' + e2.item
from equation e2
where e2.fk = e.fk and e2.type = 'Element'
order by e2.item
for xml path ('')
), 1, 1, '') as elements
from (select distinct e.fk from equation e) e
)
select e.fk, '' as elements_found, 1 as lev
from eq
where elements = ''
union all
select eq.fk, substring(elements_found, charindex(',', elements_found + ',') + 1), 2 as lev
from eq join
cte
on cte.elements_found like eq.fk + ',%' and eq.fk = cte.fk
where eq.type = 'Element'
)
select cte.fk, max(lev)
from cte
group by cte.fk
order by max(lev);
0
source to share