Shortest Distance in a Plane


  • 0

    Click here to see the full article post


  • 0
    X

    The following will only calculate n*(n-1)/2 points.
    SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))),2) AS shortest
    FROM
    point_2d p1
    JOIN
    point_2d p2 ON (p1.x <= p2.x AND p1.y <=p2.y)
    and (p1.x != p2.x or p1.y != p2.y)


  • 0

    @xinyulrsm No, it won't if you notice the MIN() function .


  • 0
    X

    @Mr.Bin Right. I mean the " (p1.x <= p2.x AND p1.y <=p2.y)
    and (p1.x != p2.x or p1.y != p2.y)" part after "ON" for join clause. This will only return n*(n-1)/2 points.


  • 0
    W

    @xinyulrsm if p1 is (1, 0) and point p2 is (2, -1), then your algorithm won't be able to satisfy your join conditions.

    Here is one that works, which doesn't have duplicate combinations:

    SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))),2) AS shortest
    FROM
    point_2d p1
    JOIN
    point_2d p2 ON ((p1.x < p2.x) OR (p1.x = p2.x AND p1.y < p2.y))
    AND (p1.x != p2.x OR p1.y != p2.y)


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.