MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Getting Max Column value from each Row

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Ravi
Beginner


Joined: 27 Jun 2005
Posts: 88
Topics: 2

PostPosted: Thu Jan 12, 2006 9:30 am    Post subject: Getting Max Column value from each Row Reply with quote

I have a table with the below data.

I need to get the max value column of that row. All the four columns will are ints. I felts its a simple query but am going nuts. Embarassed
Code:

max_data = max(col1, col2, col3, col4)

col1 col2 col3 col4 max_data
11   33   22   01   33 
404  202  303  101


Hope this is clear
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Jan 12, 2006 9:52 am    Post subject: Reply with quote

Ravi,

Try this

Code:

SELECT (CASE WHEN COL1 >= COL2 AND                 
                  COL1 >= COL3 AND                 
                  COL1 >= COL4 THEN COL1           
       ELSE                                       
          (CASE WHEN COL2 >= COL1 AND             
                     COL2 >= COL3 AND             
                     COL2 >= COL4 THEN COL2       
           ELSE                                   
              (CASE WHEN COL3 >= COL1 AND         
                         COL3 >= COL2 AND         
                         COL3 >= COL4 THEN COL3   
               ELSE                               
                  (CASE WHEN COL4 >= COL1 AND     
                             COL4 >= COL2 AND     
                             COL4 >= COL4 THEN COL4
                   END)                           
               END)                               
           END)                                   
       END)                                       
  FROM TABLE                                         
  ;                                               


Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
SureshKumar
Intermediate


Joined: 23 Jan 2003
Posts: 211
Topics: 21

PostPosted: Thu Jan 12, 2006 3:34 pm    Post subject: Reply with quote

Ravi,
I ran a quick test, had no problems. I am not sure what we are missing.

SELECT COL1,COL2,COL3,MAX(COL1,COL2,COL3) AS MAX_DATA FROM TEST

Code:

                         MAX
  COL1    COL2    COL3    DATA
------  ------  ------  ------
    10      20      30      30
     5      15      25      25
    25      35      45      45
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Jan 12, 2006 3:51 pm    Post subject: Reply with quote

SureshKumar,

Simple and good one suresh. My solutions sucks Crying or Very sad

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Ravi
Beginner


Joined: 27 Jun 2005
Posts: 88
Topics: 2

PostPosted: Thu Jan 12, 2006 6:02 pm    Post subject: Reply with quote

Yup, working in DB2 Idea Thanks SureshKumar.
Code:

 SELECT DATE('2006-01-01') AS DATE1,
        DATE('2006-02-01') AS DATE2,
        DATE('2006-03-01') AS DATE3,
  MAX (DATE('2006-01-01'),DATE('2006-02-01'),DATE('2006-03-01')) MAX_DT
  FROM SYSIBM.SYSDUMMY1
---------+---------+---------+---------+---------
DATE1       DATE2       DATE3       MAX_DT
---------+---------+---------+---------+---------
2006-01-01  2006-02-01  2006-03-01  2006-03-01

Kolusu, For me still your solution is the other option for me. Am using Sybase and its giving an error for MAX. Embarassed I have like 7 tables and all have lastChangedDate. I need to get max of lastChangedDate of the 7 the tables. All the tables are joins, so the final record should contain the max last changed date.
Or I need to use 7 Update statements with respective keys.
Code:

UPDATE table1 set table1.lastChangedDate = table2.lastChangedDate
  FROM table1, table2
 WHERE table1.key = table2.key
   AND table1.lastChangedDate < table2.lastChangedDate

UPDATE table1 set table1.lastChangedDate = table3.lastChangedDate
  FROM table1, table3
 WHERE table1.key = table2.key
   AND table1.lastChangedDate < table3.lastChangedDate

...
...
7 tables.

But I think CASE statement logic is more efficient even tough it looks as its having many conditions. Only few will match and 7 times update might be costly. approx 7*n iterations for all the 7 tables.

Can we think of another solutions.

Edited by me: Here t1 is just a key column. Needed MAX(t2,t3,t4,t5)
Code:
create table #mytemp(t1 int,t2 int,t3 int,t4 int,t5 int)

insert into #mytemp values (1,35,40,55,12)
insert into #mytemp values (2,20,35,5,70)
insert into #mytemp values (3,40,50,20,42)
insert into #mytemp values (4,80,40,55,12)

--Approach 1
SELECT A, MAX(B)
FROM (
select t1 as A, t2  as B from #mytemp
union
select t1 as A, t3  as B from #mytemp
union
select t1 as A, t4  as B from #mytemp
union
select t1 as A, t5  as B from #mytemp) C
group by A

--Approach 2
create table #temp1(tid int, tval int)

insert into #temp1(tid,tval) (select t1, t2 from #mytemp)
insert into #temp1(tid,tval) (select t1, t3 from #mytemp)
insert into #temp1(tid,tval) (select t1, t4 from #mytemp)
insert into #temp1(tid,tval) (select t1, t5 from #mytemp)

select tid,max(tval) from #temp1
group by tid

--Approach 3
select t1,case when (t2 >= t3 and t2 >= t4 and t2 >= t5) then t2
            when (t3 >= t2 and t3 >= t4 and t3 >= t5) then t3
            when (t4 >= t2 and t4 >= t3 and t2 >= t5) then t4
            else t5
       end
from #mytemp
Which Approach is better and is not costly Question
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group