October 29, 2011 at 1:28 am
Hi. i have two table that have relation.
CommentId
Title
Content
...
..
HitsId
Hit(int)
CommentId
CreatedTime
...
my tables are like above.Comments and Hits. i need to write a query. it finds Comments that has most hitting between two time interval. How can i write a query like. i try to use group by. but i can't make.
Thanks in advance.
October 29, 2011 at 2:01 am
Sorry to say but you haven't provided us with enough information to really help you.
We the DDL for the tables, sample data for the tables (enough to represent the problem domain, usually 5 to 15 rows depending on the nature of the data), expected results based on the sample data, and what you have tried so far.
Please read the first article I reference below in my signature block regarding asking for help. It will show what and how to post the information we need to really help you.
October 31, 2011 at 3:06 pm
Others have explained what you need to provide, but I'll give you a few hints.
If you have the specified time interval, that should be in your WHERE clause, having a > and < to limit the rows.
If you are looking for the most comments, then think about using a COUNT to get the number of comments, and perhaps an order by with the count desc.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 31, 2011 at 8:46 pm
Because I enjoy a challenge, here's some suggested code that may help! This will return 1 line for each Comments record with the sum of the hits counter between dates.
CREATE TABLE #Comments (
CommentId INT PRIMARY KEY CLUSTERED
, Title NVARCHAR(50)
, Content NVARCHAR(max))
;
CREATE TABLE #Hits (
HitsId INT PRIMARY KEY CLUSTERED
, Hits INT
, CommentId INT
, CreatedTime DATETIME);
INSERT #Comments
VALUES
(1, 'First comment', 'Text description for comment 1')
, (2, 'Second Comment', 'Inane description here');
INSERT #Hits
VALUES
(1, 20, 1, '2011-11-01 09:00')
, (2, 10, 2, '2011-11-01 09:00')
, (3, 15, 1, '2011-11-01 10:00')
, (4, 15, 2, '2011-11-01 10:00')
, (5, 10, 1, '2011-11-01 11:00')
, (6, 20, 2, '2011-11-01 11:00');
SELECT
c.CommentId
, MAX(c.Title)
, SUM(h.Hits) AS Hits
FROM #Comments c
INNER JOIN #Hits h
ON c.CommentId = h.CommentId
WHERE h.CreatedTime BETWEEN '2011-11-01 09:00' AND '2011-11-01 10:59'
GROUP BY c.CommentId;
DROP TABLE #Comments;
DROP TABLE #Hits;
If this isn't helpful, then feel free to follow the excellent advice from Joe and Steve above and post a script which creates the tables and sample data.
Given the simplicity of this, I'd have to wonder if it was a homework assignment.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply