asked on

tSQL : pull most recent comment by groupID for a particular date

Greetings,

I have a table like the following:

[Blog]
id
ParentId
Title
Comment
Entered

Which is joining to some other tables, but that part I think I can handle.

What I'm trying to do is get back the most recent blog comment for a blog on a particular date, sorted by the most recent comment. The ParentID goes back to the same table.

The Group By doesn't work because Comment is unique data.

The Blog (parentId one) itself might have been posted weeks ago, so it has to be sourced at the comment (Parent ID IS NULL).

Here is what I need to get out of it:

BlogCommentId (the id of the comment)
BlogParentId (the id of the parent)
BlogParentTitle (the text of the parent, aka the blog title itself)
Comment (the blog comment itself)

So that when I have my final output, I have a sidebar that shows what's going on for that day like this:

MOST RECENT BLOG COMMENTS
-----------------------------------
This is my first blog post (links to blog)
Most recent comment from Joe @ 12:45pm
[ why, yes, that is your first blog ]

Did anyone see the game last night (links to blog)
Most recent comment from Bob @ 12:43pm
[ yeah, that was awesome ]

How is everyone today (links to blog)
Most recent comment from Bill @ 12:15pm
[ awesome ]

* The sorting should be most recent COMMENT
* only the most recent comment based on parent should show (that is to say, if there are many comments, I don't care.. so if 5 blogs were commented on, with 300 replies to those 5, only 5 rows are returned)
* I do need the ID of the blog post (the parent) and would like the comments ID

The attached code brings back everything for that day. I just want the top 1 by parent ID. I am using a function to bring back the blog comments parent ID and Title, and then doing a SPLIT() when it comes back in .NET, but would rather not unless necessary. I'd like this to be performance wise fairly quick, and real time if possible (would rather not have to cache for performance sake).

Make sense?

ALTER PROCEDURE Today_BlogComments
(
@DatePicked datetime
)
AS
 
SELECT 
	b.Id,
	au.UserId,
	au.UserName,
	SUBSTRING(b.body,1,75) AS Comment,
	dbo.GetBlogUIDTitleByID(b.ParentId) AS BlogIdAndTitle
	
	FROM Blog b
	INNER JOIN aspnet_Users au ON b.UserId=au.UserId
	WHERE Convert(Varchar(10),b.Entered,111) = @DatePicked
	AND b.ParentId>0
 
	GROUP BY b.id, au.userId
 
	ORDER BY b.Entered DESC
 
 
 
	
	WHERE Convert(Varchar(10),b.Entered,111) = @DatePicked
	AND b.RemovedFromHomepage=0
	AND b.PublicView=1
	AND b.RemovedInappropriate=0
	AND b.ParentId=0
 
	ORDER BY b.Entered DESC

Open in new window

Guy Hengel [angelIII / a3]

this kind of query should help getting the data you want:

select t.*
  from [Blog] t
 where t.entered = ( select max(i.entered) from [Blog] i where i.parentid = t.parentid )

Open in new window

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NauticalNonsense

ASKER

appari, I see where you're going here... however my result set looks similar / the same.

I'm getting all the comments for the day, the row_ID is 1 for everything.

in the part:
partition by b.id, au.userid

that b.id is actually the comments ID, not the blog titles ID.. I switched the Partition By to b.ParentId by itself... Looks like it's giving me a smaller record set, appears to be right.

Two followups:
1.) Is the row_number() over(Partition by an expensive solution? That is to say, is it going to pull everything for that date, and then filter it (which it looks like it's doing, actually, i'm fairly sure of it)... or is there another, more SQL efficient way of doing something like this?

2.) Do you have any thoughts on the function thing? Does that cost more than trying to join the Blog table back in to itself and spitting it out that way? Now that my group by is gone, the function seems like it might not be necessary... maybe.

Thanks so much for your speedy reply. And any furthering of my SQL knowledge would be appreciated. This is the one area where I struggle, and although I have a solution, I'd like to understand better (teach a man to fish....)

Attached code for future reference for those interwebbers.

Thanks again appari.

ALTER PROCEDURE Today_BlogComments
(
@DatePicked datetime
)
AS
 
Select 
ID, Entered, UserID, UserName, Comment, BlogIdAndTitle, row_ID
From 
(
SELECT 
	b.Id,
	b.Entered,
	au.UserId,
	au.UserName,
	SUBSTRING(b.body,1,75) AS Comment,
	dbo.GetBlogUIDTitleByID(b.ParentId) AS BlogIdAndTitle,
	row_number() over(partition by b.parentid order by b.Entered DESC) row_ID
	
	FROM Blog b
	INNER JOIN aspnet_Users au ON b.UserId=au.UserId
	WHERE Convert(Varchar(10),b.Entered,111) = @DatePicked
	AND b.ParentId>0
)
A where A.row_ID=1
ORDER BY Entered DESC

Open in new window

NauticalNonsense

ASKER

You are what makes EE work well.

NauticalNonsense

ASKER

Yeah, this works hella nice now I think.

ALTER PROCEDURE Today_BlogComments
(
@DatePicked datetime
)
AS
 
Select 
ID, ParentId, Entered, UserID, UserName, Title, row_ID, Comment
-- BlogIdAndTitle
From 
 
(
SELECT 
	b.Id,
	b.Entered,
	bp.ID AS ParentId,
	bp.Title,
	au.UserId,
	au.UserName,
	SUBSTRING(b.body,1,75) AS Comment,
	-- dbo.GetBlogUIDTitleByID(b.ParentId) AS BlogIdAndTitle,
	row_number() over(partition by b.parentid order by b.Entered DESC) row_ID
	
	FROM Blog b
	INNER JOIN aspnet_Users au ON b.UserId=au.UserId
	INNER JOIN blog bp ON b.ParentId = bp.Id
	
	WHERE Convert(Varchar(10),b.Entered,111) = @DatePicked
	AND b.ParentId>0
)
A where A.row_ID=1
ORDER BY Entered DESC

Open in new window