Latest entry SQL problem

I have two tables:

UserTable contains (UserID, UserName) and StoryTable contains (StoryID, UserID(foreignkey), StoryName, InsertedDate)

How can I query to get each User Name along with the latest story name that he has posted ? (I m new to queries so kindly excuse if its quite basic)

I tried:

SELEC开发者_高级运维T a.Username, b.StoryName FROM [dbo].[UserTable] as A INNER JOIN [dbo].[StoryTable] as b ON a.UserID = b.UserID WHERE InsertedDate = MAX(InsertedDate) group by a.UserName;

but it throws error in sql server 2008.

---

Change your query to be this:

SELECT a.Username, b.StoryName 
FROM [dbo].[UserTable] as A 
INNER JOIN [dbo].[StoryTable] as b ON a.UserID = b.UserID 
WHERE b.InsertedDate = 
      (SELECT MAX(InsertedDate) FROM [StoryTable] AS z WHERE z.UserID = A.UserID)

---

Edited as per comment:

SELECT a.Username, b.StoryName 
FROM [dbo].[UserTable] as A 
INNER JOIN [dbo].[StoryTable] as b ON a.UserID = b.UserID 
WHERE b.StoryID = 
      (SELECT MAX(z.StoryID) FROM [StoryTable] AS z WHERE z.UserID = A.UserID)

---

SELECT Top 1 a.Username, b.StoryName 
FROM [dbo].[UserTable] as A 
INNER JOIN [dbo].[StoryTable] as b ON a.UserID = b.UserID 
order by b.InsertedDate desc

---

MAX is an aggregate function, to filter using an aggregate function, you need to use the HAVING keyword instead of WHERE

---

You can do like this

SELECT       u.Username, s.StoryName
FROM         [dbo].[UserTable] AS u
CROSS APPLY  (SELECT TOP 1 StoryName  
              FROM         [dbo].[StoryTable] AS ss
              WHERE        ss.UserID = u.UserID 
              ORDER BY     ss.InsertedDate DESC
             ) AS s