SQL rank is not working properly

Question

I am trying to use the SQL Rank () function to get a list of the best entries of several groups. Here's what doesn't work for them:

select hc.hId, hc.DpId, hc.Rank 
from (
    select d.hId, DpId, Rank()
        OVER (Partition by DpId ORDER BY d.hId) AS Rank
    FROM CurDp d
    INNER JOIN HostList h on d.DpId = h.hId
    INNER JOIN Coll_hList pch on d.hId = pch.hId
    where h.Model = 'PRIMARY'  
) hc where hc.Rank <= 10

I get the top 10 entries as follows:

 HId    | DpId | Rank
 -------x------x------
  7     | 590  | 1
  18    | 590  | 2
  23    | 590  | 3
  24    | 590  | 4
  26    | 590  | 5
  36    | 590  | 6
  63    | 590  | 7
  80    | 590  | 8
  84    | 590  | 9
  88    | 590  | 10

But when I use CROSS APPLY, what function do I need, because I have to get such records on different models, I use this code:

select pch.hId, cc.DpId, cc.Rank from from Coll_hList pch
cross apply
(
    select hc.hId, hc.DpId, hc.Rank 
    from (
        select d.hId, DpId, Rank()
            OVER (Partition by DpId ORDER BY d.hId) AS Rank
        FROM CurrDp d
        INNER JOIN HostList h on d.DpId = h.hId
        where h.Model = 'PRIMARY' and d.hId = pch.hId
    ) hc where hc.Rank <= 10
) cc

Here I always get rank 1 and it does not filter anything (without showing the whole result):

HId    | DpId | Rank
-------x------x------
7        590    1
18       590    1
23       590    1
24       590    1
26       590    1
36       590    1
63       590    1
80       590    1
84       590    1
88       590    1
124      590    1
125      590    1
133      590    1

Am I doing it wrong? Is it because of CROSS APPLY?

I also used dense_rank () instead of rank (), but it shows the same result.

Any help to achieve this request with CROSS APPLY would be greatly appreciated.

thanks

Answer 1

Coll_hList 10 , .

apply-sub-select . .

:

select pch.hId, cc.DpId, Rank()
            OVER (Partition by cc.DpId ORDER BY cc.hId) AS Rank 
from  Coll_hList pch
cross apply
(
        select d.hId, DpId
        FROM CurrDp d
        INNER JOIN HostList h on d.DpId = h.hId
        where h.Model = 'PRIMARY' and d.hId = pch.hId

) cc