Software // Information Management
Commentary
3/25/2005
08:13 PM
Joe Celko
Joe Celko
Commentary
50%
50%

An Old Celko Puzzle

If you go over to http://www.dbdebunk.com/page/page/666711.htm, you will find a letter to the editor from someone named PV about an old column of mine. Here is the jist of it: Back in June of 1996, Jack Wells submitted this SQL problem to my SQL FOR SMARTIES column.

If you go over to http://www.dbdebunk.com/page/page/666711.htm, you will find a letter to the editor from someone named PV about an old column of mine. Here is the jist of it:

Back in June of 1996, Jack Wells submitted this SQL problem to my SQL FOR SMARTIES column.His situation is pretty typical for SQL programmers who work with 3GL people. The programmers are writing a report on the employees, and they want information about each employee's current and previous salary status. The report will show the date of their promotion and the salary amount.

Jack spoke with Fabian Pascal, the week he was working on this problem, and Mr. Pascal replied that this query could not be done. He said, 'In a truly relational language it could be done, but since SQL is not relational it isn't possible, not even with SQL-92.' Sounds like a challenge to me!

Oh, I forgot to mention an addition constraint; the answer had to be in 1996 Oracle, which had no proper outer joins, no general scalar subexpressions, and so on), so your query had to run under the old SQL-86 or SQL-89 rules back then. Assume that you have this test data:

CREATE TABLE Salaries (emp_id_id CHAR(10) NOT NULL, sal_date DATE NOT NULL, sal_amt DECIMAL (8, 2) NOT NULL, PRIMARY KEY (emp_id, sal_date));

INSERT INTO Salaries VALUES ('Tom', '1996-06-20', 500.00); INSERT INTO Salaries VALUES ('Tom', '1996-08-20', 700.00); INSERT INTO Salaries VALUES ('Tom', '1996-10-20', 800.00); INSERT INTO Salaries VALUES ('Tom', '1996-12-20', 900.00); INSERT INTO Salaries VALUES ('Dick', '1996-06-20', 500.00); INSERT INTO Salaries VALUES ('Harry', '1996-07-20', 500.00); INSERT INTO Salaries VALUES ('Harry', '1996-09-20', 700.00);

Tom has had two promotions, Dick is a new hire, and Harry has had one promotion. My old solution was:

SELECT S0.emp_id, S0.sal_date, S0.sal_amt, S1.sal_date, S1.sal_amt FROM Salaries AS S0, Salaries AS S1 WHERE S0.emp_id = S1.emp_id AND S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_id = S2.emp_id) AND S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_id = S3.emp_id AND S3.sal_date < S0.sal_date) UNION ALL SELECT S4.emp_id, MAX(S4.sal_date), MAX(S4.sal_amt), NULL, NULL FROM Salaries AS S4 GROUP BY S4.emp_id HAVING COUNT(*) = 1;

+

Comment  | 
Print  | 
More Insights
The Agile Archive
The Agile Archive
When it comes to managing data, donít look at backup and archiving systems as burdens and cost centers. A well-designed archive can enhance data protection and restores, ease search and e-discovery efforts, and save money by intelligently moving data from expensive primary storage systems.
Register for InformationWeek Newsletters
White Papers
Current Issue
InformationWeek Tech Digest, Dec. 9, 2014
Apps will make or break the tablet as a work device, but don't shortchange critical factors related to hardware, security, peripherals, and integration.
Video
Slideshows
Twitter Feed
InformationWeek Radio
Archived InformationWeek Radio
Join us for a roundup of the top stories on InformationWeek.com for the week of December 14, 2014. Be here for the show and for the incredible Friday Afternoon Conversation that runs beside the program.
Sponsored Live Streaming Video
Everything You've Been Told About Mobility Is Wrong
Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.