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