Solved

Help with Max function

Posted on 2014-10-24
8
137 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 33

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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 33

Accepted Solution

by:
ste5an earned 250 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 48

Expert Comment

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

Expert Comment

by:ste5an
ID: 40401924
Sure?
0
 
LVL 48

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 69

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query with Sum and Detail rows 2 62
Help  needed 3 44
Subquery in the where statement always shows up null in the result 5 44
Can I skip a node in XML? 9 35
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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