August 5, 2016 at 6:03 am
Hi,
DECLARE @Table TABLE(
QRSID INT,
Title VARCHAR(20),
ParentID INT,
ResolutionId INT
)
INSERT INTO @Table SELECT 1,'Title1',0,4
INSERT INTO @Table SELECT 2,'Title2',0,4
INSERT INTO @Table SELECT 3,'Title3',1,4
INSERT INTO @Table SELECT 4,'Title4',1,3
INSERT INTO @Table SELECT 5,'Title5',1,4
INSERT INTO @Table SELECT 6,'Title6',2,3
INSERT INTO @Table SELECT 7,'Title6',2,4
INSERT INTO @Table SELECT 8,'Title6',0,1
INSERT INTO @Table SELECT 9,'Title6',0,3
INSERT INTO @Table SELECT 10,'Title6',9,3
SELECT m.QRSID, m.Title,m.ParentID, m.ResolutionId, q.QRSID as ResponseId, q.Title as Response
FROM @Table m
JOIN @Table q
ON m.ParentID = 0 and m.ResolutionId=4 and q.ParentID=m.Qrsid and q.ResolutionId=4
QRSID Title ParentID ResolutionId ResponseId Response
----------- -------------------- ----------- ------------ ----------- --------------------
1 Title1 0 4 3 Title3
1 Title1 0 4 5 Title5
2 Title2 0 4 7 Title6
When I run the above query it is fetching all the records whose resolution Id as 4 but i want only the final record to be included in the list.
Please see the result below. In the above result set I don't want to have the record with ResponseId 3. I want to have only the final response record that is Response Id 5 whose parent is 1.
Thanks,
Vijay
August 5, 2016 at 7:04 am
vijay-367524 (8/5/2016)
Hi,DECLARE @Table TABLE(
QRSID INT,
Title VARCHAR(20),
ParentID INT,
ResolutionId INT
)
INSERT INTO @Table SELECT 1,'Title1',0,4
INSERT INTO @Table SELECT 2,'Title2',0,4
INSERT INTO @Table SELECT 3,'Title3',1,4
INSERT INTO @Table SELECT 4,'Title4',1,3
INSERT INTO @Table SELECT 5,'Title5',1,4
INSERT INTO @Table SELECT 6,'Title6',2,3
INSERT INTO @Table SELECT 7,'Title6',2,4
INSERT INTO @Table SELECT 8,'Title6',0,1
INSERT INTO @Table SELECT 9,'Title6',0,3
INSERT INTO @Table SELECT 10,'Title6',9,3
SELECT m.QRSID, m.Title,m.ParentID, m.ResolutionId, q.QRSID as ResponseId, q.Title as Response
FROM @Table m
JOIN @Table q
ON m.ParentID = 0 and m.ResolutionId=4 and q.ParentID=m.Qrsid and q.ResolutionId=4
QRSID Title ParentID ResolutionId ResponseId Response
----------- -------------------- ----------- ------------ ----------- --------------------
1 Title1 0 4 3 Title3
1 Title1 0 4 5 Title5
2 Title2 0 4 7 Title6
When I run the above query it is fetching all the records whose resolution Id as 4 but i want only the final record to be included in the list.
Please see the result below. In the above result set I don't want to have the record with ResponseId 3. I want to have only the final response record that is Response Id 5 whose parent is 1.
Thanks,
Vijay
Looking at the code and data, the results of your query are correct. With that, how do you determine that ResponseId 5 is the final response, is it because it is the maximum value for that pair of data?
August 5, 2016 at 7:09 am
Using the assumption I made above, here is my shot at a solution:
DECLARE @Table TABLE(
QRSID INT,
Title VARCHAR(20),
ParentID INT,
ResolutionId INT
);
INSERT INTO @Table SELECT 1,'Title1',0,4;
INSERT INTO @Table SELECT 2,'Title2',0,4;
INSERT INTO @Table SELECT 3,'Title3',1,4;
INSERT INTO @Table SELECT 4,'Title4',1,3;
INSERT INTO @Table SELECT 5,'Title5',1,4;
INSERT INTO @Table SELECT 6,'Title6',2,3;
INSERT INTO @Table SELECT 7,'Title6',2,4;
INSERT INTO @Table SELECT 8,'Title6',0,1;
INSERT INTO @Table SELECT 9,'Title6',0,3;
INSERT INTO @Table SELECT 10,'Title6',9,3;
SELECT * FROM @Table;
WITH basedata as (
SELECT
m.QRSID,
m.Title,
m.ParentID,
m.ResolutionId,
q.QRSID as ResponseId,
q.Title as Response,
rn = row_number() over (partition by m.QRSID order by q.QRSID desc)
FROM
@Table m
JOIN @Table q
ON m.ParentID = 0 and
m.ResolutionId = 4 and
q.ParentID = m.Qrsid and
q.ResolutionId = 4
)
select
bd.QRSID,
bd.Title,
bd.ParentID,
bd.ResolutionId,
bd.ResponseId,
bd.Response
from
basedata bd
where
bd.rn = 1;
Please ask questions if you don't understanding anything. You should also lookup the row_number function in Books Online.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply