Lowest Data Value within a sql row

I have a table defined as

OfficeLocID int
DistanceHeadquarters int
DistanceOffice1 int
DistanceOffice2 int
DistanceOffice3 int
DistanceOffice4 int
DistanceOffice5 int

I need to write a query that for each row, will return which field has the lowest value and what field it is

(Note: No 2 distances would have same value)

For example if the data was

OfficeLocID DistanceHeadquarters DistanceOffice1 DistanceOffice2 DistancetOffice3 DistanceOffice4 DistanceOffice5
1                       23                                      30                            40                       50                              20                    32
2                       12                                      50                            40                       51                              20                    32

I would want results to be

OfficeLocID     ClosestLocation                             ClosestLocationDistance
1                        DistanceOffice4                                        20  
2                        DistanceHeadquarters                           12
johnnyg123Asked:
Who is Participating?
 
ksrsrinivasanConnect With a Mentor Commented:
One possible solution is to unpivot and select the minimum distance and do a lookup

DECLARE @tbl TABLE
(
    OfficeLocID int,
    DistanceHeadquarters int,
    DistanceOffice1 int,
    DistanceOffice2 int,
    DistanceOffice3 int,
    DistanceOffice4 int,
    DistanceOffice5 int
)

INSERT INTO @tbl
(
    OfficeLocID,
    DistanceHeadquarters,
    DistanceOffice1,
    DistanceOffice2,
    DistanceOffice3,
    DistanceOffice4,
    DistanceOffice5
)
VALUES
(
    1,
    23,
    30,
    40,
    50,
    20,
    32
),
(
    2,
    12,
    50,
    40,
    51,
    20,
    32
)

SELECT ref.OfficeLocID, MIN(un.value) [ClosestLocationDistance],
CASE MIN(un.value)
    WHEN DistanceHeadquarters THEN N'DistanceHeadquarters'
    WHEN DistanceOffice1 THEN N'DistanceOffice1'
    WHEN DistanceOffice2 THEN N'DistanceOffice2'
    WHEN DistanceOffice3 THEN N'DistanceOffice3'
    WHEN DistanceOffice4 THEN N'DistanceOffice4'
    WHEN DistanceOffice5 THEN N'DistanceOffice5'
END AS [ClosestLocation]
FROM @tbl unpivot(value for DistanceDesc in (DistanceHeadquarters,
    DistanceOffice1,
    DistanceOffice2,
    DistanceOffice3,
    DistanceOffice4,
    DistanceOffice5)) un
INNER JOIN
    @tbl ref
ON
    ref.OfficeLocID = un.OfficeLocID
GROUP BY
    ref.OfficeLocID,
    ref.DistanceHeadquarters,
    ref.DistanceOffice1,
    ref.DistanceOffice2,
    ref.DistanceOffice3,
    ref.DistanceOffice4,
    ref.DistanceOffice5

Open in new window

0
 
johnnyg123Author Commented:
Excellent!!
0
All Courses

From novice to tech pro — start learning today.