TOP1 in CROSS JOIN (SQL SERVER)

Question

I have table with child(position x, position y) and with parent(position x, position y) in sql server. What I want is to find closest parent to every child. I can do it "bad way", but probably there is a solution without using any loops.

That`s my code:

SELECT 
    child.idChild, child.x, child.y, 
    parent.idParent, parent.x, parent.y,
    sqrt(power(child.x - parent.x, 2) + power(child.y - parent.y, 2)) as distance
FROM 
    child
CROSS JOIN 
    parent
ORDER BY 
    idChild, distance

Ok, that`s fine. But now I want to limit parents only to TOP1 for each child.

Thanks

Answer 1

A handy way to do this is with the window functions. To get the top row, you can use either row_number() or rank(). There is a difference when there are ties. row_number() returns only one of multiple values. rank() will return all of them.

Here is one way to write the query:

select idChild, x, y, idParent, parentx, parenty
from (SELECT child.idChild, child.x, child.y, 
             parent.idParent, parent.x as parentx, parent.y as parenty,
             ROW_NUMBER() over (partition by child.idchild
                                order by power(child.x - parent.x, 2) + power(child.y - parent.y, 2)
                               ) as seqnum
      FROM child CROSS JOIN 
           parent
     ) pc
where seqnum = 1;

I removed the sqrt() from the distance function because it is not necessary when looking for the smallest number.