Re: Efficiency; advanced/future SQL constructs

i learned early in my career that when one sees an area that needs improving, one should also provide the solution. only pointing out the problem is complaining and whining. providing the solution in the context of added benefit or value is progress.

perhaps you should undertake the effort to develop SQL extensions or even some other language altogether that will solve your problem. taking on such challenges is how the most successful people in our country became successful.

if you are unwilling to take on this challenge, the alternative is to learn how to provide a solution with tools aleady available, which would be learning how to use SQL to obtain the desired result. what you appear to be addressing is the need for aggregates, which are easily provided with proper database design and SQL (such as star schemas).

enjoy!

"Harlan Messinger" <zzzhmessinger_at_erols.com> wrote in message news:9lu8mf$3r6$1_at_bob.news.rcn.net...
> There are some operations that are so obvious and frequent that I'm amazed
> that they didn't show up in SQL, and that I haven't read anything about
 any
> planned updates to the standards that might incorporate them. Actually,
> before I ask anything else, let me ask this--is an update in the works? Or
> is ANSI SQL now frozen for all time? Meanwhile, in the absence of a direct
> construct for these operations, I sometimes wonder what the most efficient
> way of implementing them is, whether in terms of running efficiency or
> implementation efficiency. In case it affects the answer, I work mostly
 with
> SQL Server, though sometimes Oracle comes up.
>
> One was mentioned here recently--having a batch of data to add to a table
> where each new record should be used to update an existing record if there
> is one, or to insert a record otherwise. Why wouldn't that be a standard
> operation? Given that it *isn't* a standard operation, which of the
> following is more efficient:
>
> 1. For each record, do a SELECT to find an existing record,
> and update on success, insert on failure.
>
> 2. Load the new data into a temporary table, do an UPDATE of the form
>
> update T, TEMP set T.x = TEMP.x [, ...] where T.id = TEMP.id
> [or the same thing with an INNER JOIN if supported]
>
> and then do an INSERT of the form
>
> insert into T
> select id, x [, ...] from TEMP
> where not exists (select 'x' from T where T.id = TEMP.id)
>
> (I assume this last is more efficient than
>
> where TEMP.id not in (select id from T)
>
> Correct?)
>
> Here's an example of another routine type of operation. I can write a
 query
> to get the highest annual salary of any employee in each of my company's
> divisions:
>
> select max(salary) from Employee group by divisionID
>
> But how do I *identify* the highest-paid employees?
>
> select employeeID, name, max(salary) from Employee group by divisionID
>
> obviously doesn't do it, but it would be nice to have something that
 simple.
> Even more complex though possible not quite as general would be a query to
> return all the records from the Sales table connected with the
 highest-paid
> employee in each division.
>
> What IS the best way, at this time, to get this information? IS there a
> single SQL statement that does it, in either ANSI SQL or a specific
 dialect?
>
> I'm curious what similar standard operation types others can think of that
> would be nicely handled by a standard mechanism as well.
>
>
Received on Wed Aug 22 2001 - 12:19:10 CEST