Re: Efficiency; advanced/future SQL constructs
Date: Tue, 21 Aug 2001 16:36:22 -0400
Message-ID: <3b82c45b_3_at_news3.prserv.net>
Harlan,
ANSI SQL is not frozen for all time ... however most RDBMS vendors are still struggling to get to a state of full compliance with ANSI SQL-92, and ANSI SQL-99 has probably an order of magnitude more additions (a number of them object oriented) that some vendors are beginning to address, but for large part Microsoft, to the best of my knowledge, is not currently actively looking at.
Re: your questions:
Option #2 is usually the preferred (i.e. better performance) approach.
select e1.*
from Employee as e1
join (select divisionID, salary = max(salary)
from Employee
group by divisionID) as e2
on (e1.divisionID = e2.divisionID and
e1.salary = e2.salary)
select e1.*
from Employee as e1
where e1.salary = (select top 5 e2.salary
from Employee as e2
where e2.divisionID = e1.divisionID
order by e2.salary desc)
order by e1.divisionID asc, e1.salary desc
Please note that since you did not post table schemas (CREATE TABLEs) and sample data (INSERTs), the queries posted are untested, and might contain errors. Also please note that the last query uses SQL Server proprietary syntax. One can accomplish the same using ANSI SQL, but it is both a bit more difficult and a bit slower.
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.
"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 Tue Aug 21 2001 - 22:36:22 CEST