CODEWITHSUNDEEP
sql-servert-sql
smoothumut
smoothumut

Reputation: 3491

get the id of grouped and aggregated min value of specific column sql server

how can I get the closest branch of a business according to the table below

BranchId BusinessId Distance
--      ---         ---
24      267         221.71464641964 
13      258         253.099943313603
14      258         253.099943313603
15      258         17.3790669756556
17      260         3456.74117699001
32      260         9.852770682035
121     260         374.907804283969
122     260         374.907804283969
127     260         213.074377898838
63      292         18.6199088005564
64      292         9.35737878020064
65      292         3456.74117699001

so the result would be like this

BranchId BusinessId Distance
--       ---        ---
24       267        221.71464641964
15       258        17.3790669756556
32       260        9.852770682035
64       292        9.35737878020064

Upvotes: 0

Views: 30

Answers (1)

forpas
forpas

Reputation: 164089

You can use window functions:

select distinct
  first_value(BranchId) over (partition by BusinessId order by Distance) BranchId,
  BusinessId,
  min(Distance) over (partition by BusinessId) Distance
from tablename

See the demo.
Results:

> BranchId | BusinessId |         Distance
> -------: | ---------: | ---------------:
>       15 |        258 | 17.3790669756556
>       24 |        267 |  221.71464641964
>       32 |        260 |   9.852770682035
>       64 |        292 | 9.35737878020064

Upvotes: 2

Related Questions