SQL to find max value across row and column

SQL to find max value across row and column

drop table t;
create table t(c1 number, c2 number, c3 number);

insert into t values(1,2,3);
insert into t values(4,5,6);
insert into t values(7,8,9);

commit;

select
max(greatest(c1,c2,c3)) col_value
from t;

---------------------------------------------------

with d as (
select 'C1' col_name,max(c1) val from t
union
select 'C2',max(c2) from t
union
select 'C3',max(c3) from t)
select *
from d
where val = (select max(val) from d);

---------------------------------------------------

with d as (select 'C1' col_name,c1 val from t
union
select 'C2',c2 from t
union
select 'C3',c3 from t)
select * from (
select col_name,case when val =  max(val) over() then val end val
from d)
where val is not null;

---------------------------------------------------

select column_name,col_val from (
select column_name,col_val, max(col_val) over() m_val
from t
UNPIVOT (col_val for column_name in (c1,c2,c3)))
where col_val = m_val

---------------------------------------------------

SQL Group employees into teams

create table team(ename varchar2(30), city varchar2(100));

insert into team values('Veda','Pune');

insert into team values('Shruthi','Pune');

insert into team values('Apoorva','Pune');

insert into team values('Karthik','Pune');

insert into team values('Jhon','Pune');

insert into team values('Shyam','Delhi');

insert into team values('Ram','Delhi');

insert into team values('Somen','Kolkata');

commit;

WITH d AS (

SELECT ename,

   city,

   ceil(ROW_NUMBER() OVER(PARTITION BY city ORDER BY ROWNUM) / 2) t 

FROM

team

)

SELECT

city,

listagg(ename, ',') team_members,

'Team'|| ROW_NUMBER() OVER(ORDER BY city) team_name

FROM d

GROUP BY city,t

ORDER BY 3;

Hierarchical EMP data in Flat format

Oracle SQL Practical question Emp Hiearchical data in Flat format

-------------------------------------------------------------

select regexp_substr(EMPNO_LIST, '\w+', 1, 1) L1_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 1) L1_MGR,

   1 LV1,

   regexp_substr(EMPNO_LIST, '\w+', 1, 2) L2_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 2) L2_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 2) is not null then 2 end LV2,       

   regexp_substr(EMPNO_LIST, '\w+', 1, 3) L3_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 3) L3_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 3) is not null then 3 end LV3,

   regexp_substr(EMPNO_LIST, '\w+', 1, 4) L4_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 4) L4_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 4) is not null then 4 end LV4,

   regexp_substr(EMPNO_LIST, '\w+', 1, 5) L5_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 5) L5_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 5) is not null then 5 end LV5                            

from (

select     trim(',' from sys_connect_by_path(ename,',')) ename_list,

   trim(',' from sys_connect_by_path(empno,',')) empno_list,

CONNECT_BY_ISLEAF ISLEAF

from emp

start with mgr is null

connect by prior empno=mgr)

where ISLEAF=1;

-------------------------------------------------------------

select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2, L3_EMPNO, L3_ENAME,LV3, L4_EMPNO, L4_ENAME, LV4,

       l5.empno L5_EMPNO,l5.ename L5_ENAME,case when l5.empno is not null then 5 end LV5

    from (

    select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2, L3_EMPNO, L3_ENAME, LV3,

           l4.empno L4_EMPNO,l4.ename L4_ENAME,case when l4.empno is not null then 4 end LV4

    from (

        select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2,

               l3.empno L3_EMPNO,l3.ename L3_ENAME,case when l3.empno is not null then 3 end LV3

        from (

            select L1_EMPNO, L1_ENAME, LV1,

                   l2.empno L2_EMPNO, l2.ename L2_ENAME, case when l2.empno is not null then 2 end LV2

            from (

                SELECT empno l1_empno,ename l1_ename,1 lv1

                FROM emp

                where mgr IS NULL) l1,

                emp l2

            where l2.mgr = l1.l1_empno) l2,

            emp l3

        where l3.mgr = l2.L2_EMPNO ) l3,

    emp l4

    where l4.mgr(+) = l3.L3_EMPNO) L4,

    emp l5

where l5.mgr(+) = l4.L4_EMPNO 

order by 1,4;

-------------------------------------------------------------