An Old Celko Puzzle - InformationWeek
Software // Information Management
3/25/2005
08:13 PM
Commentary
50%
50%
RELATED EVENTS
Breaking The Vulnerability Cycle - Key Findings from 100 CISOs
Mar 02, 2017
We surveyed 100 CISOs and security decision makers and found that today's application security tea ...Read More>>

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

+

More Insights
Threaded  |  Newest First  |  Oldest First
How Enterprises Are Attacking the IT Security Enterprise
To learn more about what organizations are doing to tackle attacks and threats we surveyed a group of 300 IT and infosec professionals to find out what their biggest IT security challenges are and what they're doing to defend against today's threats. Download the report to see what they're saying.
White Papers
Current Issue
2017 State of the Cloud Report
As the use of public cloud becomes a given, IT leaders must navigate the transition and advocate for management tools or architectures that allow them to realize the benefits they seek. Download this report to explore the issues and how to best leverage the cloud moving forward.
Video
Slideshows