THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Use VALUES clause to get the maximum value from some columns [SQL Server, T-SQL]

My ex-colleague Paul Mcmillan pointed me at a thread on Stack Overflow that demonstrated a neat T-SQL trick to get the maximum value from a collection of columns in a row. Paul had never seen it before and neither had I so I figure one or two of you out there might learn something from it too.

In short you can use the VALUES clause to effectively union the values into a dataset and get the MAX from that dataset. Better demonstrated with code:

DECLARE @t TABLE(a INT,b INT,c INT);
INSERT @t VALUES(1,2,3),(9,8,7),(4,6,5);
SELECT *
,      (  
SELECT  MAX(val)
          
FROM    (VALUES (a)
                       ,   (
b)
                       ,   (
c)
                   )
AS value(val)
       )
AS MaxVal
FROM @t;

 

I'm sure many of you knew this already but if you didn't, well, you too have learnt something today. See more uses for the VALUES clause at Interesting enhancements to the VALUES Clause in SQL Server 2008

@jamiet

P.S. Oh, this only works in SQL Server 2008 and beyond.

Published Friday, January 20, 2012 2:22 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James said:

excellent, very useful.

Thank You!

January 20, 2012 9:44 AM
 

siva said:

good one, some time i wil fact this issue

January 20, 2012 10:23 AM
 

Gianluca Sartori said:

Nice, thanks for sharing!

However, not as efficient as you would think.

See this for a faster solution:

http://www.sqlservercentral.com/Forums/FindPost1190488.aspx

January 20, 2012 6:06 PM
 

Jennyfer said:

Very nice.... thanks for sharing

January 23, 2012 2:34 AM
 

Gil said:

Before Row Constructors, I used to do that with (SELECT a UNION ALL SELECT b UNION ALL SELECT c)

January 23, 2012 7:02 PM
 

Ramakrishna Rao said:

Good one. It really helped me.

January 26, 2012 8:50 AM
 

Dattatrey Sindol (Datta) said:

Nice article Jamie !!

Here is an article by Robert Sheldon on this feature called Table Value Constructors (TVCs) - http://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/

February 7, 2012 8:45 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement