Search This Blog

How to select max row value using TSql

Someone asked me this question and it took me a while to figured out the solution. It look very simple with the new features available in SQL Server 2008.

Here is the example

 

 

if object_id('tempdb..#temp') is not null
drop table #temp

create table #temp
(
[Name] varchar(10),
[m1] int,
[m2] int,
[m3] int,
[m4] int
)

insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, 37, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N2', 35, 31, 145, 67)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N3', 34, 65, 37, 34)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N4', 34, 78, 90, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N5', 34, 47, 0, 18)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N6', 89, 36, 56, 60)


SELECT *, (
SELECT MAX(val) FROM (
VALUES (m1), (m2), (m3), (m4)
) AS value(val)) AS MaxVal
FROM #temp




Results



MaxQuery

No comments: