Solved

Help with Max function

Posted on 2014-10-24
8
138 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 34

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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

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 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 34

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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 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