CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
cs0815
cs0815

Reputation: 17388

Find top n closest places

I am using this simple correlated sub-query to find the closest place to a place (example simplified of course):

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp
(
    FromPlaceId INT NOT NULL,
    FromPlaceName [NVARCHAR](255) NOT NULL,
    ToPlaceId INT NOT NULL,
    ToPlaceName [NVARCHAR](255) NOT NULL,
    Distance FLOAT NOT NULL 
)

INSERT INTO #Temp( FromPlaceId, FromPlaceName, ToPlaceId, ToPlaceName, Distance) VALUES ( 1, N'Place1' , 2 , N'Place2' , 1.0  )
INSERT INTO #Temp( FromPlaceId, FromPlaceName, ToPlaceId, ToPlaceName, Distance) VALUES ( 1, N'Place1' , 3 , N'Place3' , 2.0  )
INSERT INTO #Temp( FromPlaceId, FromPlaceName, ToPlaceId, ToPlaceName, Distance) VALUES ( 2, N'Place2' , 1 , N'Place1' , 1.0  )
INSERT INTO #Temp( FromPlaceId, FromPlaceName, ToPlaceId, ToPlaceName, Distance) VALUES ( 2, N'Place2' , 3 , N'Place3' , 13.0  )
INSERT INTO #Temp( FromPlaceId, FromPlaceName, ToPlaceId, ToPlaceName, Distance) VALUES ( 3, N'Place3' , 1 , N'Place1' , 2.0  )
INSERT INTO #Temp( FromPlaceId, FromPlaceName, ToPlaceId, ToPlaceName, Distance) VALUES ( 3, N'Place3' , 2 , N'Place2' , 13.0  )

SELECT * FROM #Temp

SELECT 
    a.FromPlaceId,
    a.FromPlaceName,
    a.ToPlaceId,
    a.ToPlaceName,
    a.Distance
FROM #temp AS a WHERE a.Distance = 
(
    SELECT TOP 1 b.Distance FROM #Temp AS b WHERE a.FromPlaceId = b.FromPlaceId
    ORDER BY b.Distance ASC
)

I am just wondering how to get the 2 or 3 closest places. Obviously, a correlated sub query does not work in this scenario.

Upvotes: 1

Views: 41

Answers (1)

jpw
jpw

Reputation: 44881

If I understand your requirement right I think you should be able use the rank() window function like this:

SELECT * FROM (
    SELECT 
       FromPlaceId,
       FromPlaceName,
       ToPlaceId,
       ToPlaceName,
       Distance,
       rank = RANK() OVER (PARTITION BY FromPlaceId ORDER BY Distance)
    FROM #temp  
) a
WHERE rank <= 2;

This would return all the ToPlaceId for each FromPlaceId that have a distance that is in the top two closest, so if several ToPlaceId have the same distance all would be returned.

Changing your current query to use in and top 2 in the correlated query should also work, although I think the window function might perform better.

FROM #temp AS a WHERE a.Distance IN
(
    SELECT TOP 2 b.Distance

Upvotes: 2

Related Questions