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 ORDER BY
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:
Option one:
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
)
Option two:
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 TABLE
statements, indexes included. Also, what version of SQL Server?