Re: Efficiency; advanced/future SQL constructs

"Harlan Messinger" <zzzhmessinger_at_erols.com> wrote in message news:<9lu8mf$3r6$1_at_bob.news.rcn.net>...

[...]

> 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.

Something like

select employeeID, name from Employee where salary = (select max(salary) from Employee)

should work

/Lennart Received on Tue Aug 21 2001 - 23:41:35 CEST