hi, i need to construct a query that does the following: I have a table with fields: comments, with field ID, Username, CommentText, DateAndTime The query has to return the 10 users who placed the most words in the past 24 hours. or if not possible: The 10 users who placed the most comments in the last 24 hours Anyone knows if this is possible in a single sql statement?
I'm not thinking clearly but perhaps you can bring something together, here's what came to mind: SUM(), LENGTH() and GROUP BY Anyway, I am fairly sure it is possible.
I created the query for getting the top 10 usernames with most comments, but independent on the number of characters in the comments. That's the tricky part.
The 10 users who placed the most comments in the last 24 hours is easy, something like this select TOP 10 count(UserName) AS CommentCount, UserName FROM Comments WHERE DateAndTime > GetDate() - 1 group by UserName This query is for SQL Server but shouldnt be that difficult to change for any other form of SQL, GetDate() returns the current date/time, and GetDate() - 1 returns the date/time 24 hours ago. You will need to ensure you have good indexes if you have a large number records in the database. Your first request is certainly possible, although the way I would do it would use a function that counts the words, returns that value then groups by that would then be grouped upon. Although I wouldnt know how to do that in anything other than SQL Server. Jen