[PL\SQL] Increasing results

data

MNTH SM 1 10 2 20 3 30 4 40

WITH t AS (
SELECT 01 mnth,10 sm FROM dual UNION ALL
SELECT 02, 20 FROM dual UNION ALL
SELECT 03, 30 FROM dual UNION ALL
SELECT 04, 40 FROM dual
)
SELECT mnth, sm,
       SUM(sm) over(ORDER BY mnth ROWS UNBOUNDED PRECEDING) FROM t;

result

MNTH SM TOTAL 1 10 10 2 20 30 3 30 60 4 40 100

[PL\SQL] Pivot Query

data

mnth CITY SUMMA 1 11 1 1 22 25 1 33 1 2 11 52 2 22 3 2 33 3 3 11 3 3 22 65 3 33 6

WITH t AS
(
SELECT 1 mnth, 11 city, 1 summa FROM dual UNION ALL
SELECT 1 mnth, 22 city, 25 summa FROM dual UNION ALL
SELECT 1 mnth, 33 city, 1 summa FROM dual UNION ALL
SELECT 2 mnth, 11 city, 52 summa FROM dual UNION ALL
SELECT 2 mnth, 22 city, 3 summa FROM dual UNION ALL
SELECT 2 mnth, 33 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 11 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 22 city, 65 summa FROM dual UNION ALL
SELECT 3 mnth, 33 city, 6 summa FROM dual
) select city
      , sum(decode(mnth, 1, summa, 0)) month1
      , sum(decode(mnth, 2, summa, 0)) month2
      , sum(decode(mnth, 3, summa, 0)) month3
      , sum(sum(summa)) over(partition by city) total
  from t
 group by city;

result

/ mnth mnth mnth … mnth12 city 1 2 3 … 12 11 1 52 3 … x 22 25 3 65 … y 33 1 3 6 … z

[PL\SQL] How to Join on top (First) row

SELECT *
  FROM (SELECT ROWNUM t FROM DUAL CONNECT BY LEVEL < 6)
  JOIN (SELECT v, ROW_NUMBER() OVER(PARTITION BY v ORDER BY 1) rwnm
                     FROM (SELECT 1 v
                              FROM DUAL
                            UNION ALL
                            SELECT 1 FROM DUAL
                            UNION ALL
                            SELECT 2 FROM DUAL)) ON t = v
                                                    AND rwnm = 1;

Search table

SELECT *
  FROM user_all_tables
 WHERE TABLE_NAME LIKE UPPER('%ISSCR%')
       AND TABLE_NAME NOT IN (SELECT TABLE_NAME
                                FROM user_all_tables
                               WHERE TABLE_NAME LIKE '%$%'
                                     OR TABLE_NAME LIKE '%REP%');

performance

DECLARE
 l_start NUMBER;
 l_end   NUMBER;
BEGIN
 ...
 l_start := dbms_utility.get_time;
 ...
 l_end   := round((dbms_utility.get_time - l_start) / 100, 2);
 dbms_output.put_line(l_end || ' seconds...');
END;

(PL\SQL) How to get MAX(num)+DISTINCT str

CLEAR;  
with t as  
 (
 select '11' num, 'string1' str, '1' DATA from dual union all  
 select '12' num, 'string1' str, '3' DATA from dual union all  
 select '15' num, 'string1' str, '3' DATA from dual union all  
 select '25' num, 'string2' str, '10' DATA from dual union all  
 select '26' num, 'string2' str, '2' DATA from dual union all  
 SELECT '29' num, 'string2' str, '4' DATA from dual  
 )  
SELECT * FROM t  
WHERE num IN (SELECT MAX(num) FROM t tt WHERE t.str = tt.str);