CODEWITHSUNDEEP
sqlsql-server
Nitin Soni
Nitin Soni

Reputation: 97

Recursive SQL relation query

I am stuck on one SQL Query, any one could help me?, Following is my table [LEVEL]

With columns 

Id int, Name Varchar(50), ObjectId Int, ParentId Int, LevelId int

Following are the records:

INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('Test',1,NULL,1);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('3rdItem',2,1,2);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('1stItem',3,2,3);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('2ndItem',5,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('test tom',6,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('waterweg23',7,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,2,1);

I need to get the objects with parents having max Level Id.

Here record "ehs" has 2 parents i.e. (3 & 2) it should show for ehs with parent 3 only, as Object 3 has highest level id than 2.

So I want to get all the records with Parent having max levelId

Can anyone help me??

Upvotes: 0

Views: 131

Answers (2)

JoeFletch
JoeFletch

Reputation: 3960

Try this.

DECLARE @LEVEL TABLE (Id int, Name Varchar(50), ObjectId Int, ParentId Int, LevelId int)

INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('Test',1,NULL,1);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('3rdItem',2,1,2);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('1stItem',3,2,3);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('2ndItem',5,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('test tom',6,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('waterweg23',7,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,2,1);

SELECT
    l.ID,
    l.Name,
    l.ObjectID,
    l.ParentID,
    l.LevelID
FROM
    @LEVEL AS l INNER JOIN (
                        SELECT
                            ObjectID,
                            MAX(LevelID) AS LevelID
                        FROM
                            @LEVEL
                        GROUP BY
                            ObjectID
                        ) AS s
                    ON
                        l.ObjectID = s.ObjectID
                            AND
                        l.LevelID = s.LevelID
ORDER BY
    l.ObjectID

Upvotes: 0

roman
roman

Reputation: 117380

If you want recursive one, try this:

with cte as (
    select L.Name, L.ObjectId, L.LevelID, L.ParentId, LP.LevelId as ParentLevelId
    from [LEVEL] as L
        left outer join [LEVEL] as LP on LP.ObjectID = L.ParentId
    union all
    select L.Name, L.ObjectId, L.LevelID, c.ParentId, c.ParentLevelId
    from [LEVEL] as L
        inner join cte as c on c.ObjectId = L.ParentId
), cte2 as (
    select
        Name, ObjectID, ParentId, LevelId, ParentLevelId,
        row_number() over(partition by Name, ObjectID order by ParentLevelId desc) as row_num
    from cte
)
select
    Name, ObjectID, LevelId, ParentId, ParentLevelId
from cte2
where row_num = 1
order by name asc

sql fiddle

Upvotes: 1

Related Questions