Solved

Help with Max function

Posted on 2014-10-24
8
133 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 5

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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
spx for moving values to new table 5 70
Need some help wiht :CAST AS Double 11 44
MessageHandler Database in SQL way too large 4 40
Get row count of current SQL query 8 45
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now