Find the furthest distance of points

In this scenario I have a set of four points for ID 1 and 2. I need to find and select the two points that are furthest from my set points with the same ID.

The table is scripted here:

ID  name    point                                           id_point
====================================================================
1   DN700   POINT (-550493.96 -1218974.69)                  1
1   DN700   POINT (-550513.92733318976 -1218929.5493905835) 2
1   DN700   POINT (-550490.62291509821 -1218980.7209425652) 3
1   DN700   POINT (-550512.43436134933 -1218933.2777663434) 4
2   DN700   POINT (-550235.5039543492 -1219120.0737476321)  5
2   DN700   POINT (-550278.61165674869 -1219099.6880138929) 6
2   DN700   POINT (-550301.89265282557 -1219088.8117909778) 7
2   DN700   POINT (-550330.76399739366 -1219075.4882849427) 8

For ID 1, the farthest points are id_point 2 and 3. I will use it in another procedure, where I have to define the start and end point, which in this case is exactly point 2 and 3.

I know the solution is to use the STDistance() function to compare points with the same ID, then select a MAX value from the result, but I'm stuck here.

Any help is appreciated, thanks a lot!

Here is an sample of my query, which I describe in the comments. The #points_on_lin is a table which I previously scripted in question. The only difference is that the points are stored as geometry:

DECLARE @max_id int, @current_id int = 1;
SET @max_id = (SELECT MAX(ID_point) FROM #points_on_lin)
    WHILE @current_id <= @max_id
        BEGIN
            DECLARE @point1 geometry, @point2 geometry, @ID int, @IDP int;
            SET @point1 = (SELECT points FROM #points_on_lin WHERE ID_point = @current_id)
            SET @point2 = (SELECT points FROM #points_on_lin WHERE ID_point = @current_id + 1)
            SET @ID = (SELECT ID FROM #points_on_linia WHERE ID_point = @current_id)
            SET @name = (SELECT name FROM #points_on_lin WHERE ID_point = @current_id)
            SET @IDP = (SELECT ID_point FROM #points_on_lin WHERE ID_point = @current_id)
                INSERT INTO #points_dist
                    SELECT @ID, @name, @point1.STDistance(@point2), @IDP
            SET @current_ID = @current_ID +1
        END

And the results from table #points_dist are here:

ID  name    distance            IDP
1   DN700   49.3595888677907    1
1   DN700   56.228317019116     2
1   DN700   52.216799572342     3
1   DN700   334.040699536517    4
2   DN700   47.6849257758674    5
2   DN700   25.696244924673     6
2   DN700   31.7973324390265    7
2   DN700   544.411492523736    8

Solution

Try this:

;WITH CTE AS
(
    SELECT ID FROM #points_on_lin GROUP BY ID
)
SELECT CTE.ID, Point1, Point2, Distance FROM CTE
CROSS APPLY
(
    SELECT TOP 1 
        T1.id_point AS Point1, T2.id_point AS Point2, T1.Point.STDistance(T2.Point) AS Distance 
    FROM 
        #points_on_lin T1 join #points_on_lin T2 on T1.ID = T2.ID AND T1.id_point < T2.id_point
    WHERE 
        T1.ID = CTE.ID
    ORDER BY
        Distance DESC   
) A

Output:

ID  Point1  Point2  Distance
1   2       3       56.228317019116
2   5       8       105.177655821281