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.