An Old Celko Puzzle - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

IoT
IoT
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;

+

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
Comment  | 
Print  | 
More Insights
InformationWeek Is Getting an Upgrade!

Find out more about our plans to improve the look, functionality, and performance of the InformationWeek site in the coming months.

News
Becoming a Self-Taught Cybersecurity Pro
Jessica Davis, Senior Editor, Enterprise Apps,  6/9/2021
News
Ancestry's DevOps Strategy to Control Its CI/CD Pipeline
Joao-Pierre S. Ruth, Senior Writer,  6/4/2021
Slideshows
IT Leadership: 10 Ways to Unleash Enterprise Innovation
Lisa Morgan, Freelance Writer,  6/8/2021
White Papers
Register for InformationWeek Newsletters
Video
Current Issue
Planning Your Digital Transformation Roadmap
Download this report to learn about the latest technologies and best practices or ensuring a successful transition from outdated business transformation tactics.
Slideshows
Flash Poll