SQL Query to get latest user comment per blog post limited to one for each user?
I have multiple users submitting comments on multiple blog posts. Users can comment multiple times on each blog post. I need a SQL Query (sql server 2008) to get the last comment for each User given a BlogPostId.
Lets say 3 users submit a total of 10 comments on a specific blog post. For Blog Post #1, User A has submitted 5 comments, user B has submitted 2 comments, and user C has submitted 3 comments.
For a specific BlogPostId (eg. #1) how would I get the latest comment for each user limiting it to their most recent comment only (eg. one comment per user)?
The end result should produce three rows (eg.)
(User A) CommentId, BlogPostId, UserId, CommentData (User B) CommentId, BlogPostId, UserId, CommentData (User C) CommentId, BlogPostId, UserId, CommentData
There are many many ways to do it. Using a ranking function:
with cte as ( select *, row_number() over (partition by UserId order by PostedtDate desc) as rn from Comments where PostId = @postId) select * from cte where rn = 1;
using aggregates and cross apply:
with cte as ( select distinct UserId from Comments where PostId = @postId) select * from cte cross apply ( select top(1) * from Comments c where c.UserId = cte.UserId and c.PostId = @postId order by PostedDate desc);
In the end, the real important question is not how you query this information (which is trivial and you'll probably get 10 answers in 10 minutes), but how do you design your schema to make this query fast.
Use Queries to show records based on conditions – Documentation , Hands-on examples using the Query expression syntax; • Select and Order by; • Limit; • Label; • Where with Criteria; • The above SQL query will return the most recent access record for the nraboy user. This will translate to where tal1.username = ‘nraboy’ and tal1.activity_date = 2014-07-26 which will bring no results since the last sign in for nraboy was 2014-07-25.
Since it's MS SQL 2008, why not use ROW_NUMBER()
WITH last_comment AS ( SELECT ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY DateAdded) as RowIndex, * FROM BlogPost B WHERE BlogPostId = @BlogPostId ) SELECT * FROM last_comment WHERE RowIndex = 1
SQL ORDER BY, When using ORDER BY with a row limit (either through the check box on the query editor or by typing in LIMIT ), the APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse. This function returns the name of the current user. This function is equivalent to USER_NAME (). CURRENT_USER returns the name of the current security context. If CURRENT_USER executes after a call to EXECUTE AS switches context, CURRENT_USER
One of several possible solutions, with a bit of guessing towards your schema since you didn't post that information in your question:
SELECT C1.comment_id, P.blog_post_id, C1.user_id, C1.comment_data FROM Blog_Posts P LEFT OUTER JOIN Comments C1 ON C1.blog_post_id = P.blog_post_id LEFT OUTER JOIN Comments C2 ON C2.blog_post_id = C1.blog_post_id AND C2.user_id = C1.user_id AND C2.comment_date > C1.comment_date WHERE P.blog_post_id = @blog_post_id AND C2.comment_id IS NULL
If C2.comment_id is null then it must be because no later comment could be joined in, so C1 must be the latest. If there is an exact tie in the time you might get two comments back for the same user.
SQL Cheat Sheet and Query Syntax| Sisense, A legacy operator for older Oracle databases is the use of (+) to support outer joins. select * from users, SQL>create user <username that you want to set> identified by <password you want to set>; User Created. but again after user is created, you need to grant few rights for him/her. SQL>grant connect,resource to <created username>; Grant Succeeded. Now, just close the application and you can login back with your new username and password.
You can try this:
select * from comments where blogpost_id = 3333 and comment_id in (select max(comment_id) from comments where blogpost_id = 3333 group by UserId)
SQL Comments, SQL Comments. Comments are The following example uses a single-line comment as an explanation: /*Select all the columns To ignore just a part of a statement, also use the /* */ comment. query_posts() is a way to alter the main query that WordPress uses to display posts. It does this by putting the main query to one side, and replacing it with a new query. To clean up after a call to query_posts, make a call to wp_reset_query(), and the original main query will be restore
select Top (1) CommentID, Comment, UserName, CreateDate from comments where BlogPostID = 1 Order by CreateDate, UserName, Comment, CommentID group by UserName
OK this is just straight from my head without knowing your database You need to get all the comments for your blogpost then you need to sort by the date the comment is created either asc or desc then you need to group it for the user and pick the first from the list... depending how you sort you can also choose the last, too...
Top N Per Group in BigQuery, Reddit Top 10 Users By Comment Score for July, 2015. Let's start with an example: SELECT author To do this you can use the following query: UPDATE wp_posts SET post_author = (SELECT ID FROM wp_users WHERE user_login = '[new_author_login]') WHERE post_author = (SELECT ID FROM wp_users WHERE user_login = '[old_author_login]'); 3. Bulk Delete Spam Comments
Beginner SQL Tutorial: Learn SQL Basics While Analyzing Bike , Learn the basics of SQL and databases while analyzing a data set on bike With a relational database, like the one in the above diagram, you avoid all of these issues. We will use LIMIT a lot in our queries on the Hubway database in this Thanks very much for very useful info sharing, Allison. How have "SQL Patch" to interact with other STB object - SQL Plan Baseline? I mean, if I try to add some hints (using method described above) to a particular query with already allowed SQL Plan Baseline, have this to modify or to disallow the existent Baseline?
Managing PostgreSQL users and roles, The next step is to grant the readonly role access to run select on the required tables. SQL. GRANT However, that same pool could be a stunning place to be, full of refreshing water on a sunny day if you know how to swim. Similarly, a huge database could be fun and useful if you know these 10 most basic and simple queries of SQL. Research has proven that these 10 queries are only 3% of entire query set which can be formulated in SQL.
SELECT LIMIT 1 per column value?, That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id SELECT DISTINCT ID, u.user_login, u.user_nicename, u.user_email FROM wp_users u, wp_usermeta m WHER Stack Exchange Network Stack Exchange network consists of 175 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.