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 |
|
bpsintl
Posting Yak Master
132 Posts |
 Posted - 2009-04-28 : 15:04:13
|
| I have 2 tablesCREATE TABLE [dbo].[admin_topics]( [topic_id] [int] IDENTITY(1,1) NOT NULL, [title] [varchar](50) NULL, [topic_contents] [varchar](4000) NULL, [forumid] [int] NULL, [memberid] [int] NULL, [user_type] [tinyint] NULL, [users_name] [varchar](50) NULL, [photo] [varchar](50) NULL, [topic_date] [datetime] NULL, [sticky] [tinyint] NULL, [locked] [tinyint] NULL, [viewed] [int] NULL, [clubid] [int] NULL, CONSTRAINT [PK_admin_topics] PRIMARY KEY CLUSTERED CREATE TABLE [dbo].[admin_posts]( [post_id] [int] IDENTITY(1,1) NOT NULL, [topicid] [int] NULL, [memberid] [int] NULL, [user_type] [tinyint] NULL, [users_name] [varchar](50) NULL, [photo] [varchar](50) NULL, [post_date] [datetime] NULL, [post_title] [varchar](50) NULL, [post_body] [varchar](4000) NULL, [sticky] [tinyint] NULL, [private] [tinyint] NULL, [clubid] [int] NULL, CONSTRAINT [PK_admin_posts] PRIMARY KEY CLUSTERED What I want is a query that will get top 5 topics (ordered by topic_id desc) along with the top 5 posts from those topics.So I will see the newest 5 topics with the newest 5 posts in those topics, if that makes sense! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-04-28 : 15:32:39
|
[code]select * from(select top 5 * from admin_topics order by topic_id desc) as tjoin(select *, row_number() over (partition by post_id order by post_id desc) as rownum from admin_posts) as pon t.topic_id = p.topicid and p.rownum between 1 and 5[/code]As an approach - not testedWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
bpsintl
Posting Yak Master
132 Posts |
Posted - 2009-04-29 : 16:00:20
|
| Thanks for that, I think it does it. Just testing etc |
|
|
|
bpsintl
Posting Yak Master
132 Posts |
Posted - 2009-05-02 : 07:49:26
|
| Seems to have done the trick, thanks for that! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-02 : 07:56:44
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bpsintl
Posting Yak Master
132 Posts |
Posted - 2009-05-20 : 04:09:13
|
| Webfred, having used this query for a while now I've noticed that it works except for when a post is made in the topic table, but with no corresponding posts in the posts table, that topic is not brought back in the query. How can I include new topics that do not have any corresponding posts in the posts table? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-20 : 05:21:58
|
Please replace join by left join.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bpsintl
Posting Yak Master
132 Posts |
Posted - 2009-05-20 : 12:05:13
|
| Ah, didn't realise it would be so simple! Thanks |
|
|
|
|
|
|
|
|
multiple joins with top