CODEWITHSUNDEEP
sqlsql-serverselectgreatest-n-per-group
SJMan
SJMan

Reputation: 1607

Select the row with the biggest ID and a specific condition

I Have a table comments with the following structure :

CommentId (int), 
CommentText (nvarchar(max))
EditedBy (nvarchar(max))
ParentCommentId (int)
CaseId (int)

for a particular caseId I want to select the latest comment text as well as the EditedBy Column.

for example , if someone adds a comment table would be

CommentId   CommentText          EditedBy     ParentCommentId   CaseId
   1         ParentComment1         ABC          NULL               1 
   2         ParentComment2         ABC          NULL               1

now, if someone edits that comment , the table would look like 

CommentId   CommentText          EditedBy     ParentCommentId   CaseId
   1         ParentComment1       ABC          NULL               1 
   2         ParentComment2       ABC          NULL               2
   3         Comment2             DEF          1                  1

This editing can be done any number of times I want to select the latest comment as well as the history. In this case my dataset should be something like this : 

CaseId   CommentId   CommentText       Predicate
  1       3           Comment2         Created By ABC , Updated by DEF
  1       2           ParentComment2   Created By ABC

This is a simplified version of the problem . TIA

Upvotes: 3

Views: 171

Answers (3)

Biswabid
Biswabid

Reputation: 1411

you can simply use a self join to get the results:

find the query below:

select a.CaseId,b.CommentId, b.CommentText,'Created By '+a.EditedBy +', update by '+b.EditedBy as Predicate
from Comment a
inner join Comment b
on a.CaseId=b.CaseId
where a.CommentId<b.CommentId

Upvotes: -1

ughai
ughai

Reputation: 9880

You can use FOR XML PATH for creating your predicate column. Something like this.

SQL Fiddle

Sample Data

CREATE TABLE Comment
    ([CommentId] int, [CommentText] varchar(8), [EditedBy] varchar(3), [ParentCommentId] varchar(4), [CaseId] int);

INSERT INTO Comment
    ([CommentId], [CommentText], [EditedBy], [ParentCommentId], [CaseId])
VALUES
    (1, 'Comment1', 'ABC', NULL, 1),
    (2, 'Comment2', 'DEF', '1', 1);

Query

;WITH CTE AS 
(
SELECT CommentID, CommentText,EditedBy,ParentCommentID,CaseID,ROW_NUMBER()OVER(PARTITION BY Caseid ORDER BY CommentID DESC) RN
FROM Comment
), CTE2 as
(
SELECT CommentID, CommentText,EditedBy,ParentCommentID,CaseID,
  (
    SELECT
    CASE WHEN ParentCommentID IS NULL THEN 'Created By ' ELSE ', Updated By ' END
    + EditedBy
    FROM CTE C1
    WHERE C1.CaseID = C2.CaseID
    ORDER BY CommentID ASC
    FOR XML PATH('')
  ) as Predicate
FROM CTE C2
WHERE RN = 1
)
SELECT CaseID,CommentID, CommentText,Predicate FROM CTE2;

EDIT

If you do not want to repeat Updated By for each user who updated the caseid, use the following CASE

CASE WHEN ParentCommentID IS NULL THEN 'Created By ' + EditedBy + ', Updated By' ELSE '' END,
CASE WHEN ParentCommentID IS NOT NULL THEN ', ' + EditedBy ELSE '' END

Instead of 

    CASE WHEN ParentCommentID IS NULL THEN 'Created By ' ELSE ', Updated By ' END + EditedBy

Output

| CaseID | CommentID | CommentText |                      Predicate |
|--------|-----------|-------------|--------------------------------|
|      1 |         2 |    Comment2 | Created By ABC, Updated By DEF |

EDIT 2

Use Recursive CTE to achieve your expected output. Something like this.

SQL Fiddle

Query

;WITH CTEComment AS 
(
SELECT CommentID as RootCommentID,CommentID, CommentText,EditedBy,ParentCommentID,CaseID
FROM Comment
WHERE ParentCommentID IS NULL
UNION ALL 
SELECT CTEComment.RootCommentID as RootCommentID,Comment.CommentID, Comment.CommentText,Comment.EditedBy,Comment.ParentCommentID,Comment.CaseID
FROM CTEComment
INNER JOIN Comment
    ON CTEComment.CommentID = Comment.ParentcommentID
        AND CTEComment.CaseID = Comment.CaseID
), CTE as  
(
SELECT CommentID,RootCommentID,CommentText,EditedBy,ParentCommentID,CaseID,ROW_NUMBER()OVER(PARTITION BY CaseID,RootCommentID ORDER BY CommentID DESC) RN
FROM CTEComment
), CTE2 as
(
SELECT CommentID, CommentText,EditedBy,ParentCommentID,CaseID,
  (
    SELECT
    CASE WHEN ParentCommentID IS NULL THEN 'Created By ' ELSE ', Updated By ' END
    + EditedBy
    FROM CTE C1
    WHERE C1.CaseID = C2.CaseID
    AND C1.RootCommentID = C2.RootCommentID
    ORDER BY CommentID ASC
    FOR XML PATH('')
  ) as Predicate
FROM CTE C2
WHERE RN = 1
)
SELECT CaseID,CommentID, CommentText,Predicate FROM CTE2;

Output

| CaseID | CommentID |    CommentText |                      Predicate |
|--------|-----------|----------------|--------------------------------|
|      1 |         3 |       Comment2 | Created By ABC, Updated By DEF |
|      2 |         2 | ParentComment2 |                 Created By ABC |

Upvotes: 3

pankaj choudhary
pankaj choudhary

Reputation: 177

You can use this method
DECLARE @CaseId [int];
SET @CaseId=1`enter code here`
DECLARE @GeneratedBy [nvarchar](max);
SET @GeneratedBy=(SELECT  EditBy from Comment  WHERE ParentCommentId is NULL AND CaseId = @CaseId)
PRINT @GeneratedBy;
SELECT CaseId , CommentId , CommentText  , 'Created By '+@GeneratedBy+ ', Updated By ' +EditBy  AS Predicate FROM Comment
WHERE CaseId = @CaseId AND ParentCommentId=(SELECT  max(ParentCommentId) from Comment  WHERE  CaseId = @CaseId)

Upvotes: -1

Related Questions