punitnb26

 Profile Answers by punitnb26 

• Apr 30th, 2011

 

Try following query. I have used emp table as example
select ename, max(val) from
(
select ename, sal val from emp
union
select ename, comm val from emp
) max_val
group by ename

raghu iyer

 Profile Answers by raghu iyer 

• Jul 7th, 2011

 

Use this query

select col1, greatest(col1,col2)  coln from table_name;

Raghu Iyer

dbxplorer

 Profile Answers by dbxplorer 

• Jul 21st, 2011

 

Hi Raghu,

Query is right but having drawback that it doesn't consider NULL value in comparison, so doesn't show value on comparing with NULL.

Girdhar Tripathi

• Jul 26th, 2011

 

select * from (select max(sal) from emp ) max_sal,(select max(empno) from emp ) max_empno

dbxplorer

 Profile Answers by dbxplorer 

• Jul 26th, 2011

 

It's better to write that query in that way:

select max(empno),max(sal) from emp;

Above will give a result but now with performance.

Pranit

• Jul 28th, 2011

 

Code
SELECT max(col1),max(col2),max(col3),max(col4) FROM tblname

Thakkar Zankhana

• Aug 5th, 2011

 

Code
CREATE TABLE tmp_z (col1 VARCHAR2(20), col2 NUMBER, col3 NUMBER);


 


INSERT INTO tmp_z VALUES('A', 1, 3);


INSERT INTO tmp_z VALUES('B', 2, 6);


INSERT INTO tmp_z VALUES('C', 3, 4);


INSERT INTO tmp_z VALUES('D', 9, 10);


 


COMMIT;


 


SELECT Z.COL1,


       DECODE(MOD(ROWNUM, 2),


              0,


              GREATEST(Z.COL2, Z.COL3),


              LEAST(Z.COL2, Z.COL3))


  FROM TMP_Z Z;

milind88

 Profile Answers by milind88 

• Aug 10th, 2011

 

NAME COL1 COL2
---------- ---------- ----------
A 1 -3
B -2 6
C 3 -4
D 9 10

SQL> select name,
2 case
3 when col1>col2 then
4 col1
5 else
6 col2
7 end Highest
8 from aaa;

NAME HIGHEST
---------- ----------
A 1
B 6
C 3
D 10

GoroAU

 Profile Answers by GoroAU 

• Aug 17th, 2011

 

Hi,
GREATEST with NULLs

Code
WITH t AS 


(


SELECT 'A' AS COL1, 1 AS COL2, -3 AS COL3 FROM dual union ALL


SELECT 'B' ,-2   ,6  FROM dual union ALL


SELECT 'C' ,3    ,-4 FROM dual union ALL


SELECT 'D' ,9    ,10 FROM dual union ALL


SELECT 'E' ,NULL ,7  FROM dual


)


SELECT COL1, greatest(nvl(COL2,COL3),nvl(COL3,COL2)) COLn FROM t

BhushanNag

 Profile Answers by BhushanNag 

• Aug 24th, 2011

 

SELECT COL1, GREATEST(COL2, COL3) FROM max_value_column

Regards
Bhushan

fire_starter

 Profile Answers by fire_starter 

• Sep 15th, 2011

 

Hello,

For my query example I have used the table test_tbl, and the columns col, col1, col2.

It also does consider NULL value in comparison, so it will show value on comparing with NULL, and for every
row [Col, Col1, Col2 ] it displays as result
Col, MAX(Col1,Col2) mxm

Have a good day

Code
SELECT a.col, (SELECT max(b.val) FROM (SELECT col, col1 val FROM test_tbl


union SELECT col, col2 val FROM test_tbl) b WHERE a.col = b.col) mxm FROM test_tbl a;