Select Recursive :Last-Child. Possible

Select recursive :last-child. Possible?

No, unfortunately that's just about the only way to do it without modifying the HTML.

There has been at least one request for recursive versions of the :first-child and :last-child pseudo-classes, but it doesn't seem to have gained much favor. Notice it suggests nesting and repeating the pseudo-classes in the same way as in your question:

Currently, AFAIK, we can only match children up to some exact nesting level known in advance (3 in the example below):

.container > :first-child,
.container > :first-child > :first-child,
.container > :first-child > :first-child > :first-child {}

We cannot use just :first-child context selector since it would also select first children of blocks that are not first children themselves.

So we need a sort of recursive selector that matches not just first of last child, but recursively matches all first-most and last-most elements regardless of their nesting level.

Selecting last child of recursive list structure with CSS

You can use some tricks to make it.

If you are looking for last element then you can use of :nth-last-child(n) selector, this matches on every element in the nth child and it doesn't look for type or its parent.

This is achieved as it counts from the last child.

.container ul li:nth-last-child(1)
{
    color: red;
    font-size:22px;
}
.container li li {
    color: green;
    font-size:12px;
}

Look at here:
MyFiddel

Recursive query to retrieve all child ids below

Edit: As you commented below, it turned out your problem on the level was what I could not understand.

As you have probably understood, a recursive CTE is divided into 2 parts.

WITH RECURSIVE MyCTE AS (
    <Start of the recursion/loop>
    UNION
    <Step from level N to level N+1/N-1>
)

We will:

1. Change the starting point of the recursion to start from the correct manager, not the guy on top of the hierarchy.
2. Browse into the hierarchy the same way you did
3. Change the loop so that a branch is extended back to the top boss, for the purpose of calculating the level correctly.

Postgresql only allows 1 recursive CTE (not sure for other DBMS), so we will need to do 2 and 3 together. We only need a little extra caution to allow the query to start with several starting point (id_manager IN (...))

WITH RECURSIVE tree(id_root_manager, id_direct_manager, id_employee, level) AS (
    SELECT id_manager,
           id_manager,
           id_employee,
           UNNEST(ARRAY[0,1]) /* level > 0: go to the bottom, level <= 0: go to the top of the hierarchy */
    FROM   people
    WHERE  id_manager IN ('00555')
    UNION ALL
    SELECT id_root_manager,
           id_manager,
           p.id_employee,
           CASE WHEN level <= 0 THEN level-1 ELSE level+1 END
    FROM   people p
    JOIN    tree   t ON (level > 0 AND p.id_manager = t.id_employee) OR (level <= 0 AND t.id_direct_manager = p.id_employee)
)
SELECT id_root_manager, id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY id_root_manager, level

If you are not interested in the "root" manager, you may want to avoid duplicates by changing the final select to:

SELECT DISTINCT id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY level

Recursively get last record from same table parent child

Personally, I would go with this:

DECLARE @UserID int = 103;

WITH VTE AS
    (SELECT *
     FROM (VALUES (1, 100, 101),
                  (2, 101, 102),
                  (3, 102, 103),
                  (4, 103, NULL),
                  (5, 104, 109)) AS V (ID, UserID, DelegatedToUserID) ),
rCTE AS
    (SELECT V.ID,
            V.UserID,
            V.DelegatedToUserID
     FROM VTE V
     WHERE UserID = @UserID
     UNION ALL
     SELECT V.ID,
            V.UserID,
            V.DelegatedToUserID
     FROM rCTE r
          JOIN VTE V ON r.DelegatedToUserID = V.UserID)
SELECT *
FROM rCTE
WHERE rCTE.DelegatedToUserID IS NULL;

As I mentioned in the comments, however, passing 104 will return no rows as user 109 does not exist in the table.

Recursive query for postgresSQL parent/child

A recursive query is not needed to find the deepest children. Instead, one would look at entries that are not a parent (so no other child exists). Such entries ID is not included in the parent column.

You can then join this categories to other tables

SELECT *
FROM category cat
  JOIN category_dictionary cat_dic ON cat.id = cat_dic.id
WHERE NOT EXISTS
 (SELECT 1 FROM category cat2
  WHERE cat2.parent = cat.id);

Recursive CTE in SQL Server last node

Two different solutions - one works from the top down, the other from the bottom up. The top-down solution is a little more efficient. I'm not sure exactly what output you want for the intermediate rows, so I'm posting both.

DECLARE @usertype TABLE
(id int, Name varchar(20),ParentId int)

INSERT @usertype
VALUES (1 ,'world',Null),(2 ,'Region',1),(3 ,'Country',2),  
(4 ,'State',3), (5 ,'City',4),(6 ,'Street',5);   

-- Top down
WITH recCTE
AS
(
    SELECT Id, Name, ParentId, CAST(Name AS VARCHAR(MAX)) AS name_hierarchy
    FROM @UserType
    WHERE ParentId IS NULL

    UNION ALL

    SELECT u.Id, u.name, u.ParentId, u.name + ', ' + r.name_hierarchy
    FROM recCTE AS r
    JOIN @usertype AS u
    ON u.ParentId = r.Id
)
SELECT * FROM recCTE;

--Bottom up
WITH recCTE2
AS
(
    SELECT Id, Name, ParentId, CAST(Name AS VARCHAR(MAX)) AS name_hierarchy
    FROM @usertype AS u1
    WHERE NOT EXISTS (SELECT * FROM @usertype AS u2 WHERE u1.Id = u2.ParentId)

    UNION ALL

    SELECT u.Id, u.name, u.ParentId,  r.name_hierarchy+ ', ' + u.name 
    FROM recCTE2 AS r
    JOIN @usertype AS u
    ON r.ParentId = u.Id
)
SELECT * FROM recCTE2;

Your code has some additional complexity which appeared unnecessary, based on the information you've posted.

sql select parent child recursive in one field

You need to use a Recursive Common Table Expression.

There are many useful articles online.

Useful Links

Simple Talk: SQL Server CTE Basics

blog.sqlauthority: Recursive CTE

Here is a solution to your question:

   CREATE TABLE #TEST
    (
        id int not null,
        idparent int not null,
        jobno int not null
    );

    INSERT INTO #Test VALUES 
    (1,0,1),
    (2,1,2),
    (3,1,3),
    (4,0,4),
    (5,4,5),
    (6,5,6);

    WITH CTE AS (
-- This is end of the recursion: Select items with no parent
        SELECT id, idparent, jobno, CONVERT(VARCHAR(MAX),jobno) AS ListJob
        FROM #Test
        WHERE idParent = 0
    UNION ALL
-- This is the recursive part: It joins to CTE
        SELECT t.id, t.idparent, t.jobno,  c.ListJob + '/' + CONVERT(VARCHAR(MAX),t.jobno) AS ListJob
        FROM #Test t
        INNER JOIN CTE c ON t.idParent = c.id
    )
    SELECT * FROM CTE
    ORDER BY id;

Given a recursive query that starts with a child, how can I eliminate sibling and parent rows?

Here is an answer that uses a while loop instead.

Solution 1, using while loop and a temp table

/* Populating the temp table with the data */
DECLARE @recurse TABLE
(projectId INT,parent int);

INSERT INTO @recurse (projectId,parent) VALUES (1,-1);
INSERT INTO @recurse (projectId,parent) VALUES (2,-0);
INSERT INTO @recurse (projectId,parent) VALUES (3,2);
INSERT INTO @recurse (projectId,parent) VALUES (4,2);
INSERT INTO @recurse (projectId,parent) VALUES (5,-1);
INSERT INTO @recurse (projectId,parent) VALUES (6,3);
INSERT INTO @recurse (projectId,parent) VALUES (7,6);

DECLARE @recurse2 TABLE
(projectId INT,parent INT);

--Start by inserting the root element
INSERT INTO @recurse2 ( projectId, parent)
SELECT * FROM @recurse WHERE projectId = 2

--insert elements until all children have all children
WHILE EXISTS (SELECT * FROM @recurse WHERE parent IN (SELECT projectId FROM @recurse2) AND projectId NOT IN (SELECT projectId FROM @recurse2) )
BEGIN
    INSERT INTO @recurse2
    (
        projectId,
        parent
    )
    SELECT * FROM @recurse WHERE parent IN (SELECT projectId FROM @recurse2) AND projectId NOT IN (SELECT projectId FROM @recurse2)
END 

SELECT * FROM @recurse2

Solution 2
For performance you can create a intermidate table with the result from the while loop. This intermidiate table can be updated on an intervall, or as part of the creating an element in the main table. This could be either a part of your business logic or as a DB trigger.

Here is the code I would write if you wanted this as a scheduled job:

-- Populating the temp table with the data 
DECLARE @recurse TABLE
(projectId INT,parent int);

INSERT INTO @recurse (projectId,parent) VALUES (1,-1);
INSERT INTO @recurse (projectId,parent) VALUES (2,-0);
INSERT INTO @recurse (projectId,parent) VALUES (3,2);
INSERT INTO @recurse (projectId,parent) VALUES (4,2);
INSERT INTO @recurse (projectId,parent) VALUES (5,-1);
INSERT INTO @recurse (projectId,parent) VALUES (6,3);
INSERT INTO @recurse (projectId,parent) VALUES (7,6);

DECLARE @recurse2 TABLE
(projectId INT,parent INT, lvl int);

--Start by inserting all elements root at lvl 0
INSERT INTO @recurse2 ( projectId, parent, lvl ) SELECT projectId, parent, 0 FROM @recurse 
SELECT * FROM @recurse2

--insert elements until we have all parents for all elements
WHILE EXISTS (SELECT * FROM @recurse2 a WHERE lvl IN (SELECT TOP 1 lvl FROM @recurse2 b WHERE a.projectId = b.projectId ORDER BY lvl DESC) AND a.parent > 0 )
BEGIN
    --Insert the next parent for all elements that does not have a their top level parent allready
    INSERT INTO @recurse2 ( projectId, parent , lvl )
    SELECT  projectId, 
            (SELECT b.parent FROM @recurse b WHERE b.projectId = a.parent), 
            lvl + 1 
    FROM @recurse2 a WHERE lvl IN (SELECT TOP 1 lvl FROM @recurse2 b WHERE a.projectId = b.projectId ORDER BY lvl DESC) AND a.parent > 0 
END 

--Find all children to an element
SELECT * FROM @recurse2 WHERE parent = 2

The big advantage of solution #2 is that the performance should be REALY good for reads, probably even better than CTE's. Also it works equally well for reading from bottom to top, as top to bottom.

---

Related Topics