CODEWITHSUNDEEP
sqlsql-servert-sql
skjcyber
skjcyber

Reputation: 5957

Get the Location which has Max Distance from another Location

I have a table like below:

Create table Distance
(Loc1ID int not null,
Loc2ID int not null,
Distance int not null)

Insert into Distance values(7 ,8023989 ,1)
Insert into Distance values(11 ,3705843 ,1)
Insert into Distance values(14 ,3700170 ,4)
Insert into Distance values(23 ,1353297 ,5)
Insert into Distance values(23 ,1491303 ,21)
Insert into Distance values(32 ,12514 ,74)
Insert into Distance values(32 ,507545 ,25)
Insert into Distance values(75 ,7971270 ,2)
Insert into Distance values(75 ,4473476 ,1)
Insert into Distance values(75 ,3280411 ,6)
Insert into Distance values(79 ,7100087 ,7)
Insert into Distance values(81 ,7986762 ,2)
Insert into Distance values(84 ,5034 ,31)
Insert into Distance values(84 ,3672346 ,3)

I wanted to know the locations which has max distance from Loc1ID. I need the o/p in below format. So, the expected output will be

Loc1ID    Loc2ID    Distance

7         8023989   1
11        3705843   1
14        3700170   4
23        1491303   21
32        12514     74
75        3280411   6
79        7100087   7
81        7986762   2
84        5034      31

I tried below query, but it is giving me only one record.

select top 1 Loc1ID, Loc2ID, max(distance) as Distance from Distance
group by Loc1ID,Loc2ID
order by max(distance) desc

How can I proceed here? Any help is appreciated.

Upvotes: 1

Views: 163

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

For each Loc1ID, you want the Loc2ID that has the maximum distance. For this, you want to use row_number():

select d.Loc1ID, d.Loc2ID, d.distance
from (select d.*, row_number() over (partition by Loc1ID order by distance desc) as seqnum
      from Distance d
     ) d
where seqnum = 1;

If you want multiple rows when there are duplicates, use dense_rank() instead of row_number().

Upvotes: 1

DB_learner
DB_learner

Reputation: 1026

select Loc1ID, Loc2ID, distance from  
(select Loc1ID, Loc2ID, distance, 
rank() over(partition by Loc1ID order by distance desc) rn
from DISTANCE) a where rn =1

Upvotes: 4

Related Questions