Exact Coin Change Puzzle.

Suppose that you are a sales person at a cash register and you have one purchase to serve before you close. A buyer has to pay X dollars and N cents with bills only (no coins). You have lots of bills of various nomination and limited number of coins: 3 quarters, 9 dimes, 19 nickels, and 4 pennies left in the register. You are required to give the exact change (between 1 and 99 cents) using smallest number of (available) coins.

• Use a single SELECT statement
• The result should return 1 row and 4 columns indicating how many coins of each type to use
• 1 Quarter = 25 cents; 1 Dime = 10 cents; 1 Nickel = 5 cents

Sample result for a change of 63 cents:

Quarters      Dimes    Nickels    Pennies
---------- ---------- ---------- ----------
2          1          0          3

Solutions:

Solution #1: Using Math formula and MODEL clause:

For American coins one can rely on a mathematical formula to get the smallest number of coins for exact change:

Quarters: FLOOR of [Change Amount]/25
Dimes: FLOOR(([Change Amount] – 25*[Quarters])/10)
Nickels: FLOOR(([Change Amount] – 25*[Quarters]-10*[Dimes])/5)
Pennies: [Change Amount] – 25*[Quarters]-10*[Dimes] – 5*[Nickels]

One of the easiest ways to implement this strategy is to employ the MODEL clause:

WITH m AS (
SELECT 63 AS cents
FROM dual
)
SELECT cents "Change",
Q "Quarters",
D "Dimes",
N "Nickels",
P "Pennies"
FROM m
MODEL
DIMENSION BY(0 AS dummy)
MEASURES(
cents,
CAST(0 AS NUMBER(3)) AS Q,
CAST(0 AS NUMBER(3)) AS D,
CAST(0 AS NUMBER(3)) AS N,
CAST(0 AS NUMBER(3)) AS P
)
RULES (
Q[0]=FLOOR(CENTS[0]/25),
D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)

Result:

Change Quarters Dimes Nickels Pennies
63 2 1 0 3

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

WITH m AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<=99
)
SELECT cents "Change",
Q "Quarters",
D "Dimes",
N "Nickels",
P "Pennies"
FROM m
MODEL
PARTITION BY(ROWNUM AS rn)
DIMENSION BY(0 AS dummy)
MEASURES(
cents,
CAST(0 AS NUMBER(3)) AS Q,
CAST(0 AS NUMBER(3)) AS D,
CAST(0 AS NUMBER(3)) AS N,
CAST(0 AS NUMBER(3)) AS P
)
RULES (
Q[0]=FLOOR(CENTS[0]/25),
D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)
ORDER BY 1

Result:

Change Quarters Dimes Nickels Pennies
1 0 0 0 1
2 0 0 0 2
3 0 0 0 3
4 0 0 0 4
5 0 0 1 0
6 0 0 1 1
7 0 0 1 2
8 0 0 1 3
9 0 0 1 4
10 0 1 0 0
95 3 2 0 0
96 3 2 0 1
97 3 2 0 2
98 3 2 0 3
99 3 2 0 4

Solution #2: Using Enhanced Math formula:

It’s easy to see that the MOD function is very handy in determining the number of coins other than quarters (the largest):

WITH a AS (
SELECT 63 cents
FROM dual
)
SELECT a.cents "Change",
FLOOR(a.cents/25) "Quarters",
FLOOR(MOD(a.cents,25)/10) "Dimes",
FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a

Alternatively, we can see coin combinations for all change amounts from 1 to 99 cents:

WITH a AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<100
)
SELECT a.cents "Change",
FLOOR(a.cents/25) "Quarters",
FLOOR(MOD(a.cents,25)/10) "Dimes",
FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a
ORDER BY a.cents

Solution #3: Using Cartesian Product and Top Record pattern approach:

If we did not know the exact math formula, we could still count on the brute force approach – go over all possible coin permutations (Cartesian product) that sum up to the required total amount and then chose the combination with the fewest number of coins (top record pattern):

WITH r AS (
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=20
), x AS (
SELECT q.n "Quarters", d.n "Dimes", n.n "Nickels", p.n "Pennies",
RANK() OVER(ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p
WHERE q.n<=3
AND d.n<=9
AND n.n<=19 --not needed
AND p.n<=4
AND q.n*25 + d.n*10 + n.n*5 + p.n = 63 --amount of change
)
SELECT "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

WITH r AS ( -- this range is to be reused 5 times in this query
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=100
), x AS (
SELECT c.n "Change", q.n "Quarters", d.n "Dimes",
n.n "Nickels", p.n "Pennies",
RANK() OVER(PARTITION BY c.n ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p, r c
WHERE q.n<=3
AND d.n<=9
AND n.n<=19 --now it is needed
AND p.n<=4  AND q.n*25 + d.n*10 + n.n*5 + p.n = c.n --amount of change
AND c.n>0
)
SELECT "Change", "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1
ORDER BY 1

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

Expected Result:

--For N=100:

Biggest Prime Factor
--------------------
5

--For N=52:

Biggest Prime Factor
--------------------
13

--For N=21:

Biggest Prime Factor
--------------------
7

Solutions

#1: Using CTE (recursive WITH)

WITH input AS (
SELECT &N n
FROM dual
), x(num, flag) AS (
SELECT 2, CASE WHEN MOD(n, 2)=0 THEN 1 ELSE 0 END AS flag
FROM input
UNION ALL
SELECT x.num+1, CASE WHEN MOD(i.n, x.num+1)=0 THEN 1 ELSE 0 END
FROM input i, x
WHERE x.num+1<=i.n
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.num,LEVEL)=0
CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

Enter value for n: 100
old   2: SELECT &N n
new   2: SELECT 100 n

Biggest Prime Factor
--------------------
5

SQL> /
Enter value for n: 52
old   2: SELECT &N n
new   2: SELECT 52 n

Biggest Prime Factor
--------------------
13

SQL> /
Enter value for n: 21
old   2: SELECT &N n
new   2: SELECT 21 n

Biggest Prime Factor
--------------------
7

#2: Using CONNECT BY clause , version 1

WITH input AS (
SELECT &N n
FROM dual
), x AS (
SELECT LEVEL num
FROM input i
WHERE MOD(i.N, LEVEL)=0
CONNECT BY LEVEL<=i.N
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.num,LEVEL)=0
CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

#3: Using CONNECT BY clause, version 2

WITH input AS (
SELECT &N n
FROM dual
), range AS (
SELECT LEVEL num
FROM input i
CONNECT BY LEVEL <= i.N
), x AS(
SELECT r1.num
FROM range r1, range r2, input i
WHERE MOD(i.N, r1.num)=0
GROUP BY r1.num
HAVING COUNT(CASE WHEN MOD(r1.num, r2.num)=0 THEN 1 END)=2
)
SELECT MAX(num) "Biggest Prime Factor"
FROM x;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #12

With a single SELECT statement produce a list of first 10 prime numbers above a given number of N.

Expected Result: (for N=15)

Prime
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Expected Result: (for N=50)

Prime
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.

Solutions:

#1: Liming number of found prime numbers in CTE (Recursive WITH clsue)

WITH y AS (
SELECT 500 fromN
FROM dual
), x (n, cnt, flag) AS (
SELECT fromN,
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN),
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT x.cnt+CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1),
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1)
FROM x
WHERE x.cnt

#2: Limiting number of found prime numbers outside of CTE (Recursive WITH clsue)

WITH y AS (
SELECT 50 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN,
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) FROM x WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.

Elapsed: 00:00:00.02

#3: Using TABLE and MULTISET functions

WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, column_value flag
FROM y, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN) AS sys.odcinumberlist))
UNION ALL
SELECT x.n+1, column_value flag
FROM x, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) AS sys.odcinumberlist))  WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Elapsed: 00:00:00.12

#4: Using LATERAL views

WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, is_prime
FROM y, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
UNION ALL
SELECT x.n+1, is_prime
FROM x, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Elapsed: 00:00:00.11

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Round-Robin Schedule SQL Puzzle

Puzzle: With a single SELECT statement create a schedule of play for a round-robin tournament. The query should work for odd or even number of players. For odd number of players, the player with bye should be listed last for that round.

Expected Results:

4 players:

ROUND PAIRING
------ --------
1 1 vs 2
1 3 vs 4
2 1 vs 4
2 2 vs 3
3 3 vs 1
3 4 vs 2

OR

ROUND PAIRING
----- --------
1 1 vs 2
3 vs 4

2 1 vs 4
2 vs 3

3 3 vs 1
4 vs 2

5 players:

ROUND PAIRING
------ --------
1 5 vs 1
1 4 vs 2
1 3 - bye
2 1 vs 2
2 5 vs 3
2 4 - bye
3 1 vs 4
3 2 vs 3
3 5 - bye
4 2 vs 5
4 3 vs 4
4 1 - bye
5 3 vs 1
5 4 vs 5
5 2 - bye

OR

ROUND PAIRING
----- -------
1 5 vs 1
4 vs 2
3 - bye

2 1 vs 2
5 vs 3
4 - bye

3 1 vs 4
2 vs 3
5 - bye

4 2 vs 5
3 vs 4
1 - bye

5 3 vs 1
4 vs 5
2 - bye

6 players:

ROUND PAIRING
----- --------
1 5 vs 1
1 4 vs 2
1 3 vs 6
2 1 vs 2
2 5 vs 3
2 6 vs 4
3 1 vs 4
3 2 vs 3
3 5 vs 6
4 1 vs 6
4 2 vs 5
4 3 vs 4
5 3 vs 1
5 6 vs 2
5 4 vs 5

OR

ROUND PAIRING
----- -------
1 5 vs 1
4 vs 2
3 vs 6

2 1 vs 2
5 vs 3
6 vs 4

3 1 vs 4
2 vs 3
5 vs 6

4 1 vs 6
2 vs 5
3 vs 4

5 3 vs 1
6 vs 2
4 vs 5

Solution (SQL*Plus script):

accept players prompt "Enter the number of players: "
set pagesize 100
break on "ROUND" skip 1
col pairing for a10

WITH prompt AS (
SELECT &players AS oplayers
FROM dual
), x AS (
SELECT LEVEL n, oplayers,  oplayers + MOD(oplayers,2) AS players
FROM dual, prompt
CONNECT BY LEVEL<=oplayers + MOD(oplayers,2)-1
), w AS (
SELECT a.n AS rnd, 1+MOD(a.n+b.n,a.players-1) AS p,
CASE WHEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)<=a.players/2 THEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY a.n, b.n )-1
ELSE a.players-ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)
END AS match_id, a.oplayers
FROM x a, x b
UNION ALL
SELECT n, players AS p, 0 AS match_id, oplayers
FROM x
)
SELECT rnd AS "ROUND",
CASE WHEN MAX(p)>oplayers THEN  MIN(p) || ' - bye '
WHEN MOD(MAX(p)-MIN(p),2)=1 THEN MIN(p) || ' vs ' || MAX(p)
ELSE MAX(p) || ' vs ' || MIN(p)
END AS pairing
FROM w
GROUP BY rnd, match_id, oplayers
ORDER BY 1, CASE WHEN MAX(p)>oplayers THEN oplayers ELSE MIN(p) END;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to generate a random sample of numeric and alpha-numeric values

Puzzle: Generate a random sample of numeric and alpha-numeric values

This problem often arises when you need to run a test and you don’t have a table to experiment with. Not a problem any more!

Related Post: Use TRUNC function to generate various date ranges

Step 1: Generate a random list of 10 alpha-numeric values:

SELECT dbms_random.string('x',3) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-----
3TI
1JB
CIP
9SE
79K
YNZ
VEG
V0B
KPN
ILR

Step 2: Generate a random list of 10 integer values:

SELECT TRUNC(dbms_random.VALUE(-999,999)) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
----
539
153
979
689
212
267
-5
-832
-160
665

Step 3: Mix the above lists together (with equal shares):

SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-------
-513
SVA
-475
NRM
-903
G45
-654
2S3
415
0HG

Step 4: Let’s have a fairly random number of integers and strings in the output

WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd,
ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) rnd
FROM x
WHERE rk<=10

RND
----
-985
0TG
8JZ
-714
500
199
7IJ
249
RNI
F2G

Step 5: Final touch – let’s add a column that would flag integers

WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd,
ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) rnd, CASE WHEN REGEXP_LIKE(rnd,'^-?[[:digit:]]+\$') THEN 1 ELSE 0 END is_int
FROM x
WHERE rk<=10

RND      IS_INT
---- ----------
WIS           0
-558          1
0QR           0
-433          1
RB0           0
PT8           0
409           1
YOV           0
969           1
FFI           0

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week Challenge – Solutions to the 2nd Puzzle

Last week we presented the 2nd puzzle of our contest, Puzzle of the Week. Today we publish correct answers for that puzzle:
Thanks to all who accepted the challenge!

Dish washing schedule puzzle:

 Four roommate students, Anna, Betty, Carla, and Daniela decided to make a “Dish washing schedule”. Every day one of the girls should do all the dishes. The challenge is to make a schedule for the next month that will spread the responsibilities among the girls as evenly as possible. At the same time the schedule should be completely random.

Solution #1: Traditional approach for mimicking pivoted report.

WITH x AS (
SELECT FLOOR((LEVEL-1)/4) id,
LEVEL AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=32
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM x
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id

Sample output #1:

Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
2          1          4          3
6          5          8          7
11          9         10         12
14         15         16         13
20         19         18         17
24         21         22         23
28         27         26         25
31                    30         29

Sample output #2 (after re-running the same query):

Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
3          4          2          1
7          5          6          8
12          9         11         10
16         14         13         15
20         19         18         17
23         24         21         22
25         27         26         28
31         29         30

Solution #2: Using Recursive WITH clause for range generation:

WITH x(d) AS (
SELECT 1 AS d
FROM dual
UNION ALL
SELECT d+1
FROM x
WHERE d<32
), y AS (
SELECT FLOOR((d-1)/4) id,
CASE WHEN d<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN d END d,
RANK()OVER(PARTITION BY FLOOR((d-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM x
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM y
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id

Sample output #1:

Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
1          4          2          3
8          7          6          5
11         10          9         12
15         14         13         16
18         19         20         17
24         22         21         23
25         27         28         26
29                    30         31

Sample output #2 (after re-running the same query):

Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
1          4          3          2
5          6          7          8
11         12         10          9
13         15         16         14
20         19         17         18
22         23         24         21
28         26         27         25
29         30         31

Solution #3: Using PIVOT clause:

SELECT "1" AS "Anna","2" AS "Betty", "3" AS "Carla", "4" AS "Daniela"
FROM (
SELECT FLOOR((LEVEL-1)/4) id,
CASE WHEN LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN LEVEL END AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=4*CEIL(31/4)
)
PIVOT
(
MAX(d)
FOR rk IN (1,2,3,4)
)
ORDER BY id;

Sample output #1:

Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
4          1          3          2
7          6          8          5
10          9         11         12
16         14         13         15
20         19         17         18
23         22         21         24
26         25         27         28
31                    29         30

Sample output #2 (after re-running the same query):

Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
4          1          3          2
8          7          5          6
11         10         12          9
16         13         14         15
20         19         17         18
21         22         23         24
28         26         27         25
31         29         30

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.