asked on
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?
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
Microsoft SQL ServerSQL
Last Comment
jknj72
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.
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.
ASKER
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
JK
ASKER
Im returning Comments from the ItemComments table if there is a value. I want the last comment inserted, if there are multiple values
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).
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.
"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.
ASKER
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
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
)
ASKER
FYI, ItemCommentID is primary key in ItemComments table
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I decided to separate out this logic instead of trying to do it this way. Thank you both for your help
Also, Comments shouldn't be a BLOB column. If it is you'll need to convert it first to VARCHAR datatype, something like:
Open in new window