Solved

# Lowest Data Value within a sql row

Posted on 2014-07-21
180 Views
I have a table defined as

OfficeLocID 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
0
Question by:johnnyg123

LVL 6

Accepted Solution

ksrsrinivasan earned 500 total points
ID: 40209317
One possible solution is to unpivot and select the minimum distance and do a lookup

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

INSERT INTO @tbl
(
OfficeLocID,
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 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.DistanceOffice1,
ref.DistanceOffice2,
ref.DistanceOffice3,
ref.DistanceOffice4,
ref.DistanceOffice5
``````
0

Author Closing Comment

ID: 40209484
Excellent!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …