How to get top 5 rows of given Id?

Question

I have the following code:

DECLARE @Latest TABLE
(
    DomainId bigint, 
    [date] datetime2
)

DECLARE @Rows int
DECLARE @History int

INSERT INTO @Latest (DomainId, date)
SELECT DomainId, MAX(Date)
FROm DomainDetailDataHistory
GROUP BY DomainId

SELECT @Rows = COUNT(DomainId) FROM @Latest

SET @History = @Rows * 5

SELECT DISTINCT TOP(@History) 
  c.DomainId
  , c.[Date]
  , Passed 
FROM DomainDetailDataHistory c
JOIN  @Latest h ON h.DomainId = c.DomainId
WHERE c.[Date] != h.[Date]
ORDER BY c.Date DESC

This code gets me the latest inserted row given by Id and the last 5 insertions.

Problem:

This works all good when I have 5 insertions of every unique DomainId. I tried adding a new one and since the new one did not have any prior insertions the code gave me the top 7 insertions to compensate for the extra domain.

Clarity:

@History gives me how many different domains I have. I multiply this by 5 to get 5 of each.

Problem is when one of the domains do not have 5 insertions, lets say it only has one then I get more than the top 5 of the other domains.

How do I make sure that I always only get 5 of each and NOT MORE?

score 2 · Accepted Answer · answered Mar 29 '16 at 16:15

Have you looked into using a Cross Apply? This should do the trick for you, but I was unable to test a there is no data in your sample. For each Domain in the @Latest, it gives you the latest 5 results from the domainDetailDataHistory table.

SELECT  h.DomainId
    , c2.[Date]
    , c2.Passed
FROM    @Latest h
        CROSS APPLY ( SELECT TOP ( 5 )
                                c.DomainI
                              , c.[Date]
                              , c.Passed
                      FROM      DomainDetailDataHistory c
                      WHERE     c.DomainID = h.DomainId
                                AND c.[Date] != h.[date]
                      ORDER BY  c.[Date] DESC
                    ) AS c2;

How to get top 5 rows of given Id?

1 Answers1