Minimum values from the table

I need the minimum value from 3 coloumn and corresponding name for the min value, like this..

Name    val1   val2   val3
a       12       5       4
b       10       9       1
c       7        11      5
d       13       8       2

output:

Name    MIN
b        1

I wrote query to find minimum value :

select MIN(less) 
from (
    select开发者_如何学Go case 
        when val1<=val2 and val1<=val3 then val1
        when val2<=val1 and val2<=val3 then val2
        when val3<=val1 and val3<=val2 then val3 end as less from table) as low

I used alises,i want to display the corresponding name from the table...plz tell me the query...

You can do it using the UNION operator to convert the 3 column table into a single table with 1 column.

SELECT  TOP 1 Name, Val AS Min
FROM (
    SELECT  Name, val1 AS Val
    FROM    table
    UNION
    SELECT  Name, val2 AS Val
    FROM    table
    UNION
    SELECT  Name, val3 AS Val
    FROM    table
) AS sub_query
ORDER BY Val ASC

This solution has the added advantage that it is easier to maintain if the number of columns increases.

Most Concise

SELECT top 1 Name,col,val 
FROM T 
UNPIVOT ( val for col in (val1,val2,val3)) unpvt
ORDER BY val

Most Efficient (assuming these columns are indexed)

;WITH cte(Name, col, val) AS
(
SELECT TOP 1 Name, 'val1', val1
FROM T 
ORDER BY val1
UNION ALL
SELECT TOP 1 Name, 'val2', val2  
FROM T 
ORDER BY val2
UNION ALL
SELECT TOP 1 Name, 'val3', val3  
FROM T 
ORDER BY val3
)
SELECT TOP 1 Name, col, val  
FROM cte 
ORDER BY val