Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jameswoodmancy
Starting Member
13 Posts |
 Posted - 2011-03-23 : 05:30:56
|
| Hi, I have a topics table with the following columnstopic id, forum id, topic, posted, number of posts, number of viewsI also have a forum table withforum id, forum nameI want to run a query that for each forum I will return the forum name, latest topic and the number of posts and views for that topicCan I do this is one neat SQL query, or do I have to programtically loop through each forum in the forums table and then do another SQL query to find the latest topic for that forum based on the forum Id. Thats the way I was going to do it, but potentially its hitting the database x number of times..one sql query to return the data would be nice.Thanks for your help and time |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-23 : 05:35:19
|
[code]select *from ( select forumname, topic, numberofposts, numberofviews, row_no = row_number() over (partition by forumid order by posted desc) from topics t inner join forum f on t.forumid = f.forumid ) twhere t.row_no = 1[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
jameswoodmancy
Starting Member
13 Posts |
Posted - 2011-03-23 : 06:24:50
|
ahh very cool!thanks khtan! |
|
|
|
|
|
|
|
|
Finding latest topic for each forum