SQL Server syntax

I have this query but I only want the last comment returned from ItemComments table.
Could I do a MAX(ItemCommentID) to get the last comment back?

	SELECT WWT.WeekID, CONVERT(varchar(50), StartWorkDay, 101) + ' - ' + CONVERT(varchar(50), EndWorkDay, 101) as 'WeekRange', 
	CONVERT(varchar(50), StartWorkDay, 101) as 'StartWorkDay', StatusID, Comments, <--Need the last Comment entered???
		 ISNULL((SELECT COUNT(wid.WorkItemDetailID)
			 FROM WorkItem wi
				 INNER JOIN WorkItemDetails wid ON wi.WorkItemID=wid.WorkItemID
			 WHERE wi.WorkItemID = wwt.WorkItemID), 0) as 'WorkCount',
		ISNULL((SELECT COUNT(tid.TravelItemDetailID)
			 FROM TravelItem ti 
				 INNER JOIN TravelItemDetails tid ON ti.TravelItemID = tid.TravelItemID
			WHERE wwt.TravelItemID = ti.TravelItemID), 0) as 'TravelCount',
		 ISNULL((SELECT COUNT(iid.ImageItemDetailID)
			 FROM ImageItem ii 
				 INNER JOIN ImageItemDetails iid ON ii.ImageItemID = iid.ImageItemID
			  WHERE wwt.ImageItemID = ii.ImageItemID), 0) as 'ImageCount', WW.WorkWeekID as 'WeekKey', 
		CONVERT(varchar(50), StartDay, 101) as 'StartDay', CONVERT(varchar(50), EndDay, 101) as 'EndDay'
	FROM WorkWeek WW
	INNER JOIN WorkWeekTracker WWT ON WW.WorkWeekID = WWT.WorkWeekID
	LEFT JOIN ItemComments ic on wwt.WeekID = ic.WeekID and wwt.UserID=ic.UserID
	Where StatusID = @StatusID AND WWT.UserID = @UserID

Open in new window

jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you can but don't forget the respective GROUP BY clause.
Also, Comments shouldn't be a BLOB column. If it is you'll need to convert it first to VARCHAR datatype, something like:
CAST(Comments AS VARCHAR(MAX))

Open in new window

ste5anSenior DeveloperCommented:
Just a comment:

Yes, you can do this, IF and only IF this value is strictly increasing. When I need to guess: ~90% of such values are not. Even when they should be. Cause it's hard to implement and requires strict process when working with such tables, e.g. correct usage of DBCC CHECKIDENT with RESEED.
jknj72Author Commented:
Im not sure why you think the column is a BLOB? Its a varchar(255) and what Im for is how to get the records back with the last Comment entered for a specific week, if there is one!
JK
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
jknj72Author Commented:
Im returning Comments from the ItemComments table if there is a value. I want the last comment inserted, if there are multiple values
Vitor MontalvãoMSSQL Senior EngineerCommented:
Im not sure why you think the column is a BLOB?
It was just an alert because isn't the first time that I would see a Comments or Observation column created as a text data type. You can ignore my comment since you have it defined as VARCHAR(255).
ste5anSenior DeveloperCommented:
Is this addressed to my comment?

"Im returning Comments from the ItemComments table if there is a value. I want the last comment inserted, if there are multiple values "

The get the "last comment inserted", you need a sort criteria which gives you that. In over 20 years experience, I've even used this approach myself and failed. Cause there are for example restore scenarios, where you restore old data with new ID values which break the strictly increasing property. Thus MAX(ID) does not reliably work.

Also in parallel scenarios, there may not the last comment inserted is not necessarily that one you've expect.

Thus you should at least use a DateAdded column for your comment and use this column as primary sort column. This will reduce changes that changes in ID handling will result in wrong data drastically.

btw, such ID values are often artificial, thus it is so hard to implement the strictly increasing property, cause it is artificial alike.
jknj72Author Commented:
I have a CreateDate column but I am trying to get the last ItemCOmmentID from the LEFT JOIN to ItemCOmments table

I need this to return the last comment so I tried this in my LEFT JOIN
LEFT JOIN ItemComments ic on Wwt.WeekID = ic.WeekID
AND ic.ItemCommentID = 
        (
           SELECT MAX(ItemCommentID) 
           FROM ItemComments i 
           WHERE wwt.CommentID = i.ItemCommentID
        )

Open in new window

jknj72Author Commented:
FYI, ItemCommentID is primary key in ItemComments table
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to add more columns to the WHERE clause so you know you're filtering the correct records:
LEFT JOIN ItemComments ic on Wwt.WeekID = ic.WeekID
AND ic.ItemCommentID = 
        (
           SELECT MAX(ItemCommentID) 
           FROM ItemComments i 
           WHERE i.WeekID = ic.WeekID
                AND i.UserID= ic.UserID
                AND i.CommentID = ic.ItemCommentID
        )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
btw, SQL Server version? Using ROW_NUMBER() is in most cases faster than a correlated subquery.
jknj72Author Commented:
I decided to separate out this logic instead of trying to do it this way. Thank you both for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.