## How Many Ways to Solve this SQL Problem?

6 07 2011

July 6, 2011

Since there were so many unique solutions to the last blog article that posed a SQL challenge, I thought that I would try another blog article that asks a similar type of question.  Assume that someone showed you the following output:

``` C2   D
--- ---
100   0
150  50
200  50
201   1
300  99
350  50
400  50
500 100 ```

You have the following table definition, and rows in the table:

```CREATE TABLE T2 (
C1 NUMBER,
C2 NUMBER);

INSERT INTO T2 VALUES (1,100);
INSERT INTO T2 VALUES (4,150);
INSERT INTO T2 VALUES (7,200);
INSERT INTO T2 VALUES (8,201);
INSERT INTO T2 VALUES (10,300);
INSERT INTO T2 VALUES (14,350);
INSERT INTO T2 VALUES (18,400);
INSERT INTO T2 VALUES (24,500);

COMMIT;```

Assume that you know nothing other than the fact that the C2 values are listed in ascending order when sorted by column C1.  How many different ways can this particular problem be solved.  Yes, there is an easy way, but assume that you were trying to “help educate” the person who provided the requested output.

My least-shortest-path solution follows:

```SELECT
C2,
0 D
FROM
T2
WHERE
C1=(SELECT
MIN(C1)
FROM
T2)
UNION ALL
SELECT
V2.C2,
V2.C2-MAX(T2.C2) D
FROM
T2,
(SELECT
C1,
C2
FROM
T2) V2
WHERE
T2.C1<V2.C1
GROUP BY
V2.C2
ORDER BY
C2;

C2    D
---- ----
100    0
150   50
200   50
201    1
300   99
350   50
400   50
500  100

8 rows selected. ```

In the above, the row with the 0 in the D column was the hardest part of the solution.  Why would I use UNION ALL and not UNION – what was not in the specification?

This blog article was inspired by an old question found in a Usenet group from 1998 – if you were answering the question in 1998, would your answer be any different?  Be creative with your solution.  While you are thinking about a solution, take a look at this old Usenet thread and consider how difficult it was to find the “50 highest paid workers” in the last century.