HIERARCHYID, Select data in topological order but also order by name within direct subordinates

  • Hi,

    Please use the following sql to create a sample of test data to play around with.

    USE [master]

    GO

    --

    --

    -- CREATE DATABASE

    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'HID')

    BEGIN

    CREATE DATABASE [HID];

    END

    GO

    --

    --

    --

    USE [HID]

    GO

    --

    --

    -- CREATE TABLE

    IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL

    DROP TABLE dbo.Employees;

    GO

    CREATE TABLE dbo.Employees

    (

    empid INT NOT NULL,

    hid HIERARCHYID NOT NULL,

    lvl AS hid.GetLevel() PERSISTED,

    empname VARCHAR(25) NOT NULL,

    salary MONEY NOT NULL,

    CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(empid)

    );

    --

    CREATE UNIQUE CLUSTERED INDEX idx_depth_first ON dbo.Employees(hid);

    CREATE UNIQUE INDEX idx_breadth_first ON dbo.Employees(lvl, hid);

    --

    --

    -- CREATE STORED PROCEDURE

    IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL

    DROP PROC dbo.usp_AddEmp;

    GO

    CREATE PROC dbo.usp_AddEmp

    @empid AS INT,

    @mgrid AS INT = NULL,

    @empname AS VARCHAR(25),

    @salary AS MONEY

    AS

    DECLARE

    @hid AS HIERARCHYID,

    @mgr_hid AS HIERARCHYID,

    @last_child_hid AS HIERARCHYID;

    IF @mgrid IS NULL

    SET @hid = HIERARCHYID::GetRoot();

    ELSE

    BEGIN

    -- Query the HIERARCHYID of the manager and store it

    SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);

    -- Query the maximum HIERARCHYID value among the direct subordinates (i.e. immidiate children) of the manager and store it

    SET @last_child_hid = (SELECT MAX(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid);

    -- Call the GetDescendant method of HIERARCHYID for the manager's HIERARCHYID and get the HIERARCHYID for the last node and store it

    SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);

    END

    --

    INSERT INTO dbo.Employees(empid, hid, empname, salary)

    VALUES(@empid, @hid, @empname, @salary);

    GO

    --

    --

    -- CREATE SAMPLE SET OF DATA

    EXEC dbo.usp_AddEmp

    @empid = 1, @mgrid = NULL, @empname = 'David' , @salary = $10000.00;

    EXEC dbo.usp_AddEmp

    @empid = 2, @mgrid = 1, @empname = 'Eitan' , @salary = $7000.00;

    EXEC dbo.usp_AddEmp

    @empid = 3, @mgrid = 1, @empname = 'Ina' , @salary = $7500.00;

    EXEC dbo.usp_AddEmp

    @empid = 4, @mgrid = 2, @empname = 'Seraph' , @salary = $5000.00;

    EXEC dbo.usp_AddEmp

    @empid = 5, @mgrid = 2, @empname = 'Jiru' , @salary = $5500.00;

    EXEC dbo.usp_AddEmp

    @empid = 6, @mgrid = 2, @empname = 'Steve' , @salary = $4500.00;

    EXEC dbo.usp_AddEmp

    @empid = 7, @mgrid = 3, @empname = 'Aaron' , @salary = $5000.00;

    EXEC dbo.usp_AddEmp

    @empid = 8, @mgrid = 5, @empname = 'Lilach' , @salary = $3500.00;

    EXEC dbo.usp_AddEmp

    @empid = 9, @mgrid = 7, @empname = 'Rita' , @salary = $3000.00;

    EXEC dbo.usp_AddEmp

    @empid = 10, @mgrid = 5, @empname = 'Sean' , @salary = $3000.00;

    EXEC dbo.usp_AddEmp

    @empid = 11, @mgrid = 7, @empname = 'Gabriel', @salary = $3000.00;

    EXEC dbo.usp_AddEmp

    @empid = 12, @mgrid = 9, @empname = 'Emilia' , @salary = $2000.00;

    EXEC dbo.usp_AddEmp

    @empid = 13, @mgrid = 9, @empname = 'Michael', @salary = $2000.00;

    EXEC dbo.usp_AddEmp

    @empid = 14, @mgrid = 9, @empname = 'Didi' , @salary = $1500.00;

    EXEC dbo.usp_AddEmp

    @empid = 15, @mgrid = 9, @empname = 'Brandon' , @salary = $1500.00;

    EXEC dbo.usp_AddEmp

    @empid = 16, @mgrid = 9, @empname = 'Arc' , @salary = $1500.00;

    EXEC dbo.usp_AddEmp

    @empid = 17, @mgrid = 9, @empname = 'George' , @salary = $1500.00;

    EXEC dbo.usp_AddEmp

    @empid = 18, @mgrid = 9, @empname = 'Deepak' , @salary = $1500.00;

    Following query gives me the data in proper topological order.

    SELECT * FROM Employees ORDER BY hid

    But I would like is to select data in topological order as well as order by name within direct subordinates. So that would show following names under "Rita" in the order of name since they all are at the same level.

    Emilia

    Michael

    Didi

    Brandon

    Arc

    George

    Deepak

    Must be a Friday for me but I can't seem to make it work. Any help would be greatly appreciated.

    TA 🙂


    Kindest Regards,

    WRACK
    CodeLake

  • This was removed by the editor as SPAM

  • No I haven't but I was hoping to avoid that as using HIERARCHYID gives me a direct view of the tree without using CTEs due to the topological sorting key it generates.

    Any other ideas?


    Kindest Regards,

    WRACK
    CodeLake

  • No takers?


    Kindest Regards,

    WRACK
    CodeLake

  • I think the solution is not with the select but with your stored procedure usp_AddEmp. You need to insert the child with the appropriate HierarchyId. Possibly something like

    -- CREATE STORED PROCEDURE

    IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL

    DROP PROC dbo.usp_AddEmp;

    GO

    CREATE PROC dbo.usp_AddEmp

    @empid AS INT,

    @mgrid AS INT = NULL,

    @empname AS VARCHAR(25),

    @salary AS MONEY

    AS

    DECLARE

    @hid AS HIERARCHYID,

    @mgr_hid AS HIERARCHYID,

    @child_hid1 AS HIERARCHYID,

    @child_hid2 AS HIERARCHYID;

    IF @mgrid IS NULL

    SET @hid = HIERARCHYID::GetRoot();

    ELSE

    BEGIN

    -- Query the HIERARCHYID of the manager and store it

    SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);

    SET @child_hid1 = (SELECT MAX(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname < @empname );

    SET @child_hid2 = (SELECT MIN(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname > @empname );

    -- Call the GetDescendant method of HIERARCHYID for the manager's HIERARCHYID and get the appropriate HIERARCHYID and store it

    SET @hid = @mgr_hid.GetDescendant(@child_hid1, @child_hid2);

    END

    --

    INSERT INTO dbo.Employees(empid, hid, empname, salary)

    VALUES(@empid, @hid, @empname, @salary);

    GO

    Dave

  • Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Dave Brooking (4/22/2012)


    I think the solution is not with the select but with your stored procedure usp_AddEmp. You need to insert the child with the appropriate HierarchyId. Possibly something like

    -- CREATE STORED PROCEDURE

    IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL

    DROP PROC dbo.usp_AddEmp;

    GO

    CREATE PROC dbo.usp_AddEmp

    @empid AS INT,

    @mgrid AS INT = NULL,

    @empname AS VARCHAR(25),

    @salary AS MONEY

    AS

    DECLARE

    @hid AS HIERARCHYID,

    @mgr_hid AS HIERARCHYID,

    @child_hid1 AS HIERARCHYID,

    @child_hid2 AS HIERARCHYID;

    IF @mgrid IS NULL

    SET @hid = HIERARCHYID::GetRoot();

    ELSE

    BEGIN

    -- Query the HIERARCHYID of the manager and store it

    SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);

    SET @child_hid1 = (SELECT MAX(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname < @empname );

    SET @child_hid2 = (SELECT MIN(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname > @empname );

    -- Call the GetDescendant method of HIERARCHYID for the manager's HIERARCHYID and get the appropriate HIERARCHYID and store it

    SET @hid = @mgr_hid.GetDescendant(@child_hid1, @child_hid2);

    END

    --

    INSERT INTO dbo.Employees(empid, hid, empname, salary)

    VALUES(@empid, @hid, @empname, @salary);

    GO

    Dave

    Thanks Dave. I am afraid someone would come up with that answer. Appreciate it 🙂 I will try to implement it.


    Kindest Regards,

    WRACK
    CodeLake

  • Jeff Moden (4/22/2012)


    Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.

    Sorry Jeff but I am not very familier with HierarchyID. Would be able to post alternatives to HierarchyID and still retrieve the data in the manner I want? TA 🙂


    Kindest Regards,

    WRACK
    CodeLake

  • WRACK (4/22/2012)


    Jeff Moden (4/22/2012)


    Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.

    Sorry Jeff but I am not very familier with HierarchyID. Would be able to post alternatives to HierarchyID and still retrieve the data in the manner I want? TA 🙂

    Ummm... Ok. I'm confused. You setup the HierarchyID datatype for your table and I thought that meant you were knowledgable with it especially since you have a proc to add employee's to it. Aren't you kind of stuck using it now?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Almost forgot. Here's an article that explains one method to do as you ask.

    http://www.sqlservercentral.com/articles/T-SQL/72503/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I should have issued a disclaimer much the same as Jeff. I hadn't used HIERARCHYID before yesterday - we only use 2005 where I work.

    However, based on Jeff's statement that the sorting is "ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.". Will the following work (I'm currently at work and don't have access to 2008 to test this)?

    SELECT empid

    ,hid

    ,lvl

    ,empname

    ,salary

    FROM dbo.Employees

    ORDER BY hid.GetParent(1) ASC, empname ASC

    Update: I'm sure it can't be that simple. I'm having difficulty visualising the hierarchyid values, I think the query given above will break the topological hierarchy structure.

    Dave

  • This was easy, but maybe this is what you are looking for.

    SELECT * FROM Employees ORDER BY lvl desc, hid

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Jeff Moden (4/22/2012)


    WRACK (4/22/2012)


    Jeff Moden (4/22/2012)


    Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.

    Sorry Jeff but I am not very familier with HierarchyID. Would be able to post alternatives to HierarchyID and still retrieve the data in the manner I want? TA 🙂

    Ummm... Ok. I'm confused. You setup the HierarchyID datatype for your table and I thought that meant you were knowledgable with it especially since you have a proc to add employee's to it. Aren't you kind of stuck using it now?

    I am not an expert on HierarchyID. Just trying to learn following Itzik's atricle. Apologies for confusion.


    Kindest Regards,

    WRACK
    CodeLake

  • I believe this is Itzik's article http://www.sqlmag.com/content1/topic/hierarchyid/catpath/tsql3 to which WRACK refers in the previous post.

    Using Jeff's article http://www.sqlservercentral.com/articles/T-SQL/72503/ a version using the supplied table with hierarchyid is

    WITH

    cteHierarchy AS

    (

    SELECT e.empid

    ,(SELECT e2.empid FROM dbo.Employees e2 WHERE e2.hid = e.hid.GetAncestor(1)) AS ManagerId

    ,e.empname

    ,lvl

    FROM dbo.Employees e

    ),

    cteDirectReports AS

    (

    SELECT e.empid

    ,e.ManagerId

    ,e.empname

    ,lvl = 1

    ,HierarchicalPath = CAST('\'+CAST(e.empname AS VARCHAR(10)) AS VARCHAR(4000))

    FROM cteHierarchy e

    WHERE e.ManagerId IS NULL

    UNION ALL

    SELECT e.empid

    ,e.ManagerId

    ,e.empname

    ,d.lvl + 1,

    HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.empname AS VARCHAR(10)) AS VARCHAR(4000))

    FROM cteHierarchy e

    INNER JOIN cteDirectReports d ON e.ManagerId = d.empid

    )

    SELECT *

    FROM cteDirectReports

    ORDER BY HierarchicalPath

    ;

    Dave

  • Thanks Dave. I shall look into it.


    Kindest Regards,

    WRACK
    CodeLake

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply