find the total comment and last comment against serviceid

Question

create table ServiceComment(ServiceID int,CommentDate Date,CommentDetails varchar(200));

insert into ServiceComment values(1001,'2021-11-15','Test Comment1.1')

insert into ServiceComment values(1001,'2021-11-16','Test Comment1.2')

insert into ServiceComment values(1001,'2021-11-17','Test Comment1.3')

insert into ServiceComment values(1002,'2021-11-15','Test Comment2.1')

insert into ServiceComment values(1002,'2021-11-16','Test Comment2.2')

ServiceID CommentDate CommentDetails

1001 2021-11-15 Test Comment1.1

1001 2021-11-16 Test Comment1.2

1001 2021-11-17 Test Comment1.3

1002 2021-11-15 Test Comment2.1

1002 2021-11-16 Test Comment2.2

Need the following result

ServiceID TotalComment LastComment

1001 3 Test Comment1.3

1002 2 Test Comment2.2

Answer 1

There are a lot of ways to implement your SQL. You did not mention your database type so I am giving you a solution that uses SQL ANSI ( Work with all database types )

SELECT A.ServiceID, COUNT(*) AS TotalComment,

(

SELECT CommentDetails

FROM ServiceComment B

WHERE B.ServiceID = A.ServiceID

AND B.CommentDate =

(

SELECT MAX(CommentDate) FROM ServiceComment C WHERE C.ServiceID = B.ServiceID

)

) AS LastComment

FROM ServiceComment A

GROUP BY A.ServiceID

Some comments:

1. Because you need to get total comments per service -> It means you need to use GROUP BY.
2. Because you need to get last comment per service -> You need to query MAX(CommentDate) for each Service, then query the last comment based on the max comment date.