Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
Posted: Thu Jan 12, 2006 9:52 am Post subject:
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
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. 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)
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
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
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