I have got a parent table, let's call it Post, with two columns - PostId, Title. I have got another one table, let's call it Comment - CommentId, PostId (FK), CreatedDate, Type, Message
I need to get a flat view of parent and one of it's childs which will look like below:
PostId, Title, CommentId, CreatedDate, Type, Message
I need the oldest one comment in this view for each post, but I also need Type and message though I don't want to group by CreatedDate, Type and Message.
Without grouping by them I can't use
I have got two options here - using
PARTITION OVER and
CROSS/OUTER APPLY. First is not an option cause of performance gain, second one is ok but I can't for example filter by Type (
WHERE Type = 3) since it's a view.
What I've tried:
SELECT DISTINCT P.PostId, P.Title FROM dbo.Post AS P OUTER APPLY ( SELECT TOP 1 C.CommentId, C.CreatedDate, C.Type, C.Message FROM dbo.Comment C WHERE C.PostId = P.PostId ORDER BY C.CreatedDate ASC )
SELECT P.PostId, P.Title, C.CommentId, C.CreatedDate, C.Type, C.Message ROW_NUMBER() OVER (PARTITION BY p.PostId ORDER BY C.CreatedDate ASC) AS Generation FROM dbo.Post P LEFT JOIN dbo.Comment C ON C.PostId = P.PostId
and then use
WHERE Generation = 1 from application server
To be clear, the issue is that
OUTER APPLY performs great with SQL side paging (skip 100 take 100 for example), but when I need to take a count - it's a bottleneck since of each row processing
Not sure if it helps, but I need something like this:
CREATE TABLEstatements, indexes included. Also, what version of SQL Server?