?
Solved

Help with Max function

Posted on 2014-10-24
8
Medium Priority
?
139 Views
Last Modified: 2014-10-24
Hi..
Trying to update a column with the max value of 3 other columns
what is wrong with this?

UPDATE  SALES  SET HIGHEST_OF_3  
 = (SELECT MAX(v) FROM VALUES (JON), (JANE), (BOB)) AS VALUE(v))

Incorrect syntax near the keyword 'values'.


thx
0
Comment
Question by:JElster
[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
8 Comments
 
LVL 6

Expert Comment

by:Flora
ID: 40401821
(SELECT Max(v)
   FROM (VALUES (JON), (JANE), (BOB)) AS value(v)) as Maxvalue
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40401826
The syntax of the Table Value Constructor is slightly different:

UPDATE  SALES
SET     HIGHEST_OF_3 = ( SELECT MAX(v)
                         FROM   ( VALUES ( 'JON' ), ( 'JANE' ), ( 'BOB' ) ) AS V ( v )
                       );

Open in new window


There were also the quotation marks missing.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 40401841
SQL Server does not have a "greatest()" function which works across the values of a row. The following is for SQL Server 2005. Later versions have other capabilities.

You could use a case expression to compare each column, but if you have more than 3 then this is going to get tedious very quickly.

I suggest the following:
update sales 
set [HIGHEST_OF_3] = m.mp
from sales
join (
      select id, max(person) mp
      from (
            select id, [JON] as person
            from sales
            union all
            select id, [JANE] as person
            from sales
            union all
            select id, [BOB] as person
            from sales
            ) as p
      group by id
    ) as m on sales.id = m.id
;

Open in new window

See a small sample running here: http://sqlfiddle.com/#!3/124d7/4

full setup:
**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE SALES
    	([id] int identity primary key,  [JON] int, [JANE] int, [BOB] int, [HIGHEST_OF_3] int)
    ;
    	
    INSERT INTO SALES
    	([JON], [JANE], [BOB])
    VALUES
    	(11, 12, 13)
    ;

**Query 1**:

    update sales 
    set [HIGHEST_OF_3] = m.mp
    from sales
    join (
          select id, max(person) mp
          from (
                select id, [JON] as person
                from sales
                union all
                select id, [JANE] as person
                from sales
                union all
                select id, [BOB] as person
                from sales
                ) as p
          group by id
        ) as m on sales.id = m.id
    

**[Results][2]**:
    

**Query 2**:

    select
    *
    from sales
    
    

**[Results][3]**:
    
    | ID | JON | JANE | BOB | HIGHEST_OF_3 |
    |----|-----|------|-----|--------------|
    |  1 |  11 |   12 |  13 |           13 |



  [1]: http://sqlfiddle.com/#!3/124d7/4

  [2]: http://sqlfiddle.com/#!3/124d7/4/0

Open in new window

0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 35

Accepted Solution

by:
ste5an earned 1000 total points
ID: 40401877
Using a GREATEST() function like PortletPaul demonstrated for T-SQL makes no sense from the relational view point. To compare those values they must be of the same kind, thus forming a repeating group.

Thus the my approach is here normally showing this:

CREATE TABLE SALES
    (
      id INT IDENTITY
             PRIMARY KEY ,
      JON INT ,
      JANE INT ,
      BOB INT
    );
    	
INSERT  INTO SALES
        ( JON, JANE, BOB )
VALUES  ( 11, 12, 13 );

INSERT  INTO SALES
        ( JON, JANE, BOB )
VALUES  ( 200, 300, 100 );

WITH    Normalized
          AS ( SELECT   *
               FROM     SALES S UNPIVOT ( Value FOR Attribute IN ( BOB, JANE, JON ) ) U
             ),
        MaxValue
          AS ( SELECT   N.id ,
                        MAX(N.Value) AS HIGHEST_OF_3
               FROM     Normalized N
               GROUP BY N.id
             )
    SELECT  S.* ,
            MV.HIGHEST_OF_3
    FROM    dbo.SALES S
            LEFT JOIN MaxValue MV ON MV.id = S.id;
   
DROP TABLE SALES;

Open in new window


This should be done in a view. When not other possible, then use a persisted computed column and use an explicit user defined function.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40401911
There remains the issue of SQLServer 2005...
Values and pivot/unpivot dont exist in that version
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40401924
Sure?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40401962
Im on a mobile phone right now, so its by memory. Pretty sure values () doesnt, and pivot/unpivot also.

Regardless, the 3 fiekds need to be placed in a column,  them MAX ()  can be used.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40402670
If there are only 3 values, just add a computed column that uses "brute force" calc to determine it.  I assume JON can't be NULL and the others can, although the code should work regardless.

ALTER TABLE SALES
ADD HIGHEST_OF_3 AS CASE
    WHEN JANE > ISNULL(JON, '') AND JANE > ISNULL(BOB, '') THEN JANE
    WHEN BOB > ISNULL(JON, '') AND BOB > ISNULL(JANE, '') THEN BOB
    ELSE JON END
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

718 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