Archive

Posts Tagged ‘tips’

[PLSQL] Select a random row from a database table

Friday, 5 February, 2010 1 comment

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);
Advertisements
Categories: PL/SQL Tags: , ,

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

Friday, 5 February, 2010 1 comment

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);
Categories: PL/SQL Tags: , ,

[PLSQL] Increasing results

Thursday, 27 August, 2009 Leave a comment

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
Categories: PL/SQL Tags: ,

[PL\SQL] Increasing results

Thursday, 27 August, 2009 Leave a comment

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
Categories: PL/SQL Tags: ,

[PLSQL] Pivot Query

Wednesday, 26 August, 2009 Leave a comment

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
Categories: PL/SQL Tags: , ,

[PL\SQL] Pivot Query

Wednesday, 26 August, 2009 Leave a comment

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
Categories: PL/SQL Tags: , ,

[PLSQL] How to Join on top (First) row

Wednesday, 26 August, 2009 1 comment
SELECT *
  FROM (SELECT ROWNUM t FROM DUAL CONNECT BY LEVEL &lt; 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;
Categories: PL/SQL Tags: ,