Solved

Lowest Data Value within a sql row

Posted on 2014-07-21
2
182 Views
Last Modified: 2014-07-21
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
0
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
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,
    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
 

Author Closing Comment

by:johnnyg123
ID: 40209484
Excellent!!
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question