Solved

Help with Max function

Posted on 2014-10-24
8
136 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

861 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