[PLSQL] Select a random row from a database table

There is some way to select a random record or row from a database table.

SELECT col1, col2, col3  FROM (SELECT * FROM tcard SAMPLE(1) ORDER BY dbms_random.VALUE)
          WHERE branch = 1
                CANCELDATE > SYSDATE
                AND ROWNUM <= 100);

[PL\SQL] Select a random row from a database table

There is some way to select a random record or row from a database table.

SELECT col1, col2, col3  FROM (SELECT * FROM tcard SAMPLE(1) ORDER BY dbms_random.VALUE)
          WHERE branch = 1
                CANCELDATE > SYSDATE
                AND ROWNUM <= 100);

[PLSQL] 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] 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

[PLSQL] 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] 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

[PLSQL] 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;

[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; [/sourcecode]

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;

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;