Re: Efficiency; advanced/future SQL constructs

> 1. For each record, do a SELECT to find an existing record,
> and update on success, insert on failure.
This is called a MERGE statement. The paper was submitted jointly by Oracle and IBM and has been accepted for SQL4.
MERGE, in its full form will be more general than what you describe. It will e.g. also support DELETE ("If I didn't see the product in my inventory, yank it from the product list")

> select employeeID, name, max(salary) from Employee group by divisionID
SELECT employeeID, name, salary
(SELECT employeeID, name, salary,

              ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn) AS T WHERE rn = 1;

Product specific extentions like TOP 1 or FETCH FIRST n ROWS can make away with the nested query.

Other features that are accepted for SQL4 (which might interest MS SQL Server users) are IDENTITY column properties.

Cheers
Serge Received on Fri Aug 24 2001 - 14:02:32 CEST