Query items into correct order
-
I need some assistance in creating a query (some sort of recursive query).
I have the following two tables, tblObject and tblDependency. Most of the objects are built in a special order, requiring an object(s) to be complete before it is built. Per example, before building 2005_viewAccountsPayOverdue, the 2005_viewAccountsPay must be built (and before building the 2005_viewAccountsPay, the 2005_viewAccounts must be built). I need to order all of the objects such that any dependencies are listed before the object shows up in the list; therefore, any objects without a dependency shall show up first.
tblObject (in actuality, I shall have an objectType field and both the name and type shall uniquely identify the row – hence the objectID)
objectID objectName
1 2005_viewInvestors
2 2005_viewInvestorsProps
3 2005_viewInvestorPropsTax
4 2005_viewAccounts
5 2005_viewAccountsPay
6 2005_viewInvestorAccounts
7 2005_viewCompanies
8 2005_viewAccountsPayOverdue
tblDependency (both are foreign keys to tblObject.objectID)
dependent_objectIDpreceeding_objectID
21
3 2
5 4
6 1
6 4
85
Query should result in the following order:
objectIDobjectName
1 2005_viewInvestors (no dependencies)
4 2005_viewAccounts (no dependencies)
7 2005_viewCompanies (no dependencies)
2 2005_viewInvestorsProps (1 created first)
3 2005_viewInvestorPropsTax (2 created first)
5 2005_viewAccountsPay (4 created first)
6 2005_viewInvestorAccounts (1 and 4 created first)
8 2005_viewAccountsPayOverdue (5 created first)
Thanks for any guidance!
-
Assuming objectID indicates order of creation:
SELECT O.objectID
,O.objectName
FROM tblObject O
LEFT JOIN (
SELECT DISTINCT R.dependent_objectID
FROM tblDependency R ) D
ON O.objectID = D.dependent_objectID
ORDER BY
CASE WHEN D.dependent_objectID IS NULL
THEN 0
ELSE 1 END
,O.objectID
-
Object_ID not indicative of order of creation (sorry I changed names, but you might get the gist):-
SET NOCOUNT ON
DECLARE
@Objects INT
,@Built INT
,@CurrentObject INT
,@LastUnbuiltObject INT
,@Build CHAR(1)
IF OBJECT_ID('Tempdb.dbo.#Objects') IS NOT NULL
DROP TABLE dbo.#Objects
CREATE TABLE dbo.#Objects
(
[Id] INT
,Object VARCHAR(50)
)
IF OBJECT_ID('Tempdb.dbo.#Dependencies') IS NOT NULL
DROP TABLE dbo.#Dependencies
CREATE TABLE dbo.#Dependencies
(
Object INT
,DependsOn INT
)
INSERT INTO dbo.#Objects([Id],Object) VALUES (1, '2005_viewInvestors')
INSERT INTO dbo.#Objects([Id],Object) VALUES (2, '2005_viewInvestorsProps')
INSERT INTO dbo.#Objects([Id],Object) VALUES (3, '2005_viewInvestorPropsTax')
INSERT INTO dbo.#Objects([Id],Object) VALUES (4, '2005_viewAccounts')
INSERT INTO dbo.#Objects([Id],Object) VALUES (5, '2005_viewAccountsPay')
INSERT INTO dbo.#Objects([Id],Object) VALUES (6, '2005_viewInvestorAccounts')
INSERT INTO dbo.#Objects([Id],Object) VALUES (7, '2005_viewCompanies')
INSERT INTO dbo.#Objects([Id],Object) VALUES (8, '2005_viewAccountsPayOverdue')
INSERT INTO dbo.#Dependencies(Object, DependsOn) VALUES (2, 1)
INSERT INTO dbo.#Dependencies(Object, DependsOn) VALUES (3, 2)
INSERT INTO dbo.#Dependencies(Object, DependsOn) VALUES (5, 4)
INSERT INTO dbo.#Dependencies(Object, DependsOn) VALUES (6, 1)
INSERT INTO dbo.#Dependencies(Object, DependsOn) VALUES (6, 4)
INSERT INTO dbo.#Dependencies(Object, DependsOn) VALUES (8, 5)
-- Query should result in the following order:
-- objectID objectName
-- 1 2005_viewInvestors (no dependencies)
-- 4 2005_viewAccounts (no dependencies)
-- 7 2005_viewCompanies (no dependencies)
-- 2 2005_viewInvestorsProps (1 created first)
-- 3 2005_viewInvestorPropsTax (2 created first)
-- 5 2005_viewAccountsPay (4 created first)
-- 6 2005_viewInvestorAccounts (1 and 4 created first)
-- 8 2005_viewAccountsPayOverdue (5 created first)
IF OBJECT_ID('Tempdb.dbo.#Builds') IS NOT NULL
DROP TABLE dbo.#Builds
CREATE TABLE dbo.#Builds
(
[ID] INT IDENTITY(1,1)
,Object INT
)
-- Get first level objects (ie those with no dependencies)
INSERT INTO dbo.#Builds(Object)
SELECT
o.[Id]
FROM
dbo.#Objects o
LEFT JOIN dbo.#Dependencies d
ON o.[Id] = d.Object
WHERE
d.Object IS NULL
-- set loop variables
SELECT @Objects = COUNT(*) FROM dbo.#Objects
SELECT @Built = COUNT(*) FROM dbo.#Builds
-- while we still have objects to build
WHILE @Built < @Objects
BEGIN
-- set first and last row ID's of objects still unbuilt
SELECT
@CurrentObject = MIN([ID])
,@LastUnbuiltObject = MAX([ID])
FROM
dbo.#Objects
WHERE
[ID] NOT IN (SELECT [Object] FROM dbo.#Builds)
-- Process each unbuilt object
WHILE @CurrentObject <= @LastUnbuiltObject
BEGIN -- check to see if it can be built
-- by checking if this object has ANY
-- rows in dependencies where the
-- DependsOn has not yet been built
-- if so, then do not build
-- if not, then build
IF EXISTS(
SELECT *
FROM
dbo.#Dependencies
WHERE
Object = @CurrentObject
AND
DependsOn NOT IN(SELECT [ID] FROM dbo.#Builds)
 
BEGIN
SET @Build = 'N'
END
ELSE
BEGIN
SET @Build = 'Y'
END
-- if we can build
-- insert into the Builds table and exit the inner loop
IF @Build = 'Y'
BEGIN
INSERT INTO dbo.#Builds(Object)
VALUES (@CurrentObject)
BREAK
END
ELSE -- if we cannot build, reset currentobject and repeat loop to see if it can be built
BEGIN
SELECT
@CurrentObject = MIN([ID])
FROM
dbo.#Objects
WHERE
[ID] > @CurrentObject
END
END
-- re-calculate how many are now built and repeat until all objects have been processed
SELECT @Built = COUNT(*) FROM dbo.#Builds
END
SELECT
b.[ID]
,o.Object
FROM
dbo.#Builds b
INNER JOIN dbo.#Objects o
ON b.[ID] = o.[ID]
-
>> Object_ID not indicative of order of creation
There seems to be nothing else to indicate the order of creation.
Something like my query will produce the results in the order you want. Using your test data:
SELECT O.[ID]
,O.Object
FROM #Objects O
LEFT JOIN (
SELECT DISTINCT R.Object
FROM #Dependencies R ) D
ON O.[ID] = D.Object
ORDER BY
CASE WHEN D.Object IS NULL
THEN 0
ELSE 1 END
,O.[ID]
produces:
ID Object
----------- --------------------------------------------------
1 2005_viewInvestors
4 2005_viewAccounts
7 2005_viewCompanies
2 2005_viewInvestorsProps
3 2005_viewInvestorPropsTax
5 2005_viewAccountsPay
6 2005_viewInvestorAccounts
8 2005_viewAccountsPayOverdue
(8 row(s) affected)
-
Thanks for the help (sorry about the late response). I'm testing out the above information today. As per the creation order, it really is not important. I just need to ensure that anything dependent upon other objects is listed after all of its dependencies. Anything without a dependency should be listed at the top (doesn't really matter what the order is, but alphabetical would make sense).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply