?
Solved

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

Posted on 2009-02-16
5
Medium Priority
?
400 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:NauticalNonsense
  • 3
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23647740
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

0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 23647752
try this

ALTER PROCEDURE Today_BlogComments
(
@DatePicked datetime
)
AS
 
Select
ID, UserID, UserName, Comment, BlogIdAndTitle
From (
SELECT
      b.Id ID,
      au.UserId UserID,
      au.UserName UserName,
      SUBSTRING(b.body,1,75) AS Comment,
      dbo.GetBlogUIDTitleByID(b.ParentId) AS BlogIdAndTitle,
      row_number() over(partition by b.id, au.userId 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
 
0
 
LVL 7

Author Comment

by:NauticalNonsense
ID: 23647834
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

0
 
LVL 7

Author Closing Comment

by:NauticalNonsense
ID: 31547234
You are what makes EE work well.
0
 
LVL 7

Author Comment

by:NauticalNonsense
ID: 23647854
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

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question