How can I query the parent data and the top child record at the same time?

Question

Not sure how I would describe this, so I think my title might be a bit off. But it comes down to: "Please write this request for me, I'm stuck like hell"

I have two tables (only for the relevant parts)

//MyEntity -------- Id Name //MyEntLogEntries --------------- MyEntityId TimeStamp Username Content 

I want to return the list in this form

 Entity.Id,Name,"latest related log entry username WITH a username not null" 

But log entries may contain newer entries without a username. So I need the last entry with the username.

I get a list of entities and request the last entry separately, but this gives me scary N + 1 queries for the report.

Answer 1

One of the methods

 SELECT E.Id, E.Name, T.* FROM MyEntity E OUTER APPLY(SELECT TOP 1 * FROM MyEntLogEntries L WHERE L.MyEntityId = E.Id AND Username IS NOT NULL ORDER BY TimeStamp DESC) T 

And further

 ;WITH CTE AS ( SELECT E.Id, E.Name, L.MyEntityId, L.TimeStamp, L.Username, L.Content, ROW_NUMBER() OVER (PARTITION BY E.Id ORDER BY TimeStamp DESC) AS RN FROM MyEntity E LEFT JOIN MyEntLogEntries L ON L.MyEntityId = E.Id AND Username IS NOT NULL ) SELECT * FROM CTE WHERE RN=1