Select the row with the biggest ID and a specific condition

Question

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

what sql-server version do you have? what did you try so far/ — Bulat, Aug 12 '15 at 08:59
`select TOP 1 * from Comment hc INNER JOIN Comment hc1 ON hc.ParentCommentId = hc1.CommentId ORDER BY hc.CommentId DESC` It is getting me only the first comment but no predicated. This also does not work when there are multiple comments for a case. I am using SQL Server 2014 — SJMan, Aug 12 '15 at 09:01
possible duplicate of [Optimal way to concatenate/aggregate strings](http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — Bulat, Aug 12 '15 at 09:04
Have a look at these questions - http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings and http://stackoverflow.com/questions/3442931/sql-server-select-distinct-rows-using-most-recent-value-only. I think they have your answer. — Bulat, Aug 12 '15 at 09:05

ughai · Accepted Answer · 2015-08-12T10:01:41.797

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 |

this is only giving me the latest , there can also be a case where we have multiple comments in a case, please see edit — SJMan, Aug 12 '15 at 09:44
can you please update your sample data and expected output. Based on your current expected output, it looks to have a single comment per case — ughai, Aug 12 '15 at 09:47

score -1 · Answer 2 · answered Aug 12 '15 at 09:55

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)

score -1 · Answer 3 · answered Aug 12 '15 at 10:19

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

Select the row with the biggest ID and a specific condition

3 Answers3