MS SQL Server - Very simple stupid aggregate question

Author

Very simple stupid aggregate question

Prospero via SQLMonster.com

2006-01-13, 3:23 am

I dont get the way SQL does aggregates its seems so clumsy...
I just want to know a simple way to do something ridiculously simple...
say i have some table with employeeID, name, city, salary
I wanna find the highest paid employee per city.
its simple of course to just say select city, max(salary) from table group by
city
but what if i want the result set to give me the name of the guy who is the
highest paid
All i know to do is this stupid thingy here:
select name,city,salary
from table
where city=(select city,max(salary)
from table
group by city)

But this is just insane for such a simple tiny peice of information and it
seems like its gonna make SQL do a lot of extra work. putting select city,
name,max(salary) then group by city,name doesnt work cause u get the max
salary for identical combination of name and cities. Theres got to be a
better way. I just cant find the right thing to do in any books or online
help, pls clue me in.

--
Message posted via http://www.webservertalk.com

Roji. P. Thomas

2006-01-13, 3:23 am

Prospero,

If I understand your spec correct, there is no easy way to do this.

But even the query you proposed will not work.

> select name,city,salary
> from table
> where city=(select city,max(salary)
> from table
> group by city)

This will give you the error

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.

If you change the query to only return city, still you will get the error.

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If you change the query using IN as

> select name,city,salary
> from table
> where city IN(select city
> from table
> group by city)

It will return all the rows back.

You can rewrite the quesry as following

> select name,city,salary
> from table
> where salary IN(select max(salary)
> from table
> group by city)

But even thats is not guaranteed to give you the correct results, because
the
query will list all employees who's salary is equal to the max salary of ANY
city.

So to achieve the desired result, the query should be

SELECT Lastname,city,Salary

FROM Employees O
WHERE HireDate = (SELECT Max(Salary) FROM Employees I
WHERE I.City = O.City)

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

"Prospero via webservertalk.com" <u16785@uwe> wrote in message
news:5a4791606a752@u
we...
>I dont get the way SQL does aggregates its seems so clumsy...
> I just want to know a simple way to do something ridiculously simple...
> say i have some table with employeeID, name, city, salary
> I wanna find the highest paid employee per city.
> its simple of course to just say select city, max(salary) from table group
> by
> city
> but what if i want the result set to give me the name of the guy who is
> the
> highest paid
> All i know to do is this stupid thingy here:
> select name,city,salary
> from table
> where city=(select city,max(salary)
> from table
> group by city)
>
> But this is just insane for such a simple tiny peice of information and it
> seems like its gonna make SQL do a lot of extra work. putting select city,
> name,max(salary) then group by city,name doesnt work cause u get the max
> salary for identical combination of name and cities. Theres got to be a
> better way. I just cant find the right thing to do in any books or online
> help, pls clue me in.
>
> --
> Message posted via http://www.webservertalk.com

Prospero via SQLMonster.com

2006-01-13, 3:23 am

>
>SELECT Lastname,city,Salary

>FROM Employees O
>WHERE HireDate = (SELECT Max(Salary) FROM Employees I
> WHERE I.City = O.City)
>
Yeah this is what i meant and the code that I used...sorry i mistyped...why
is something so simple so difficult?is this REALLY the only way? I can deal
with it if its just a lot of coding, but is query analyzer actually deciding
to do a bunch of redundant stuff when it executes this or does it get what u
mean and do it quickly?

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200601/1

Andrey Odegov

2006-01-13, 7:23 am

Prospero,

try this:

SELECT P1.*
FROM Table AS P1
JOIN Table AS P2
ON P2.city = P1.city
AND P2.salary >= P1.salary
GROUP BY P1.employeeID, P1.name, P1.city, P1.salary
HAVING COUNT(*) = 1;

---
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)

Roji. P. Thomas

2006-01-13, 7:23 am

> SELECT Lastname,city,Salary

> FROM Employees O
> WHERE HireDate = (SELECT Max(Salary) FROM Employees I
> WHERE I.City = O.City)

Sorry. Should be

SELECT Lastname,city,Salary

FROM Employees O
WHERE Salary= (SELECT Max(Salary) FROM Employees I
WHERE I.City = O.City)

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@gmail.com> wrote in message
news:e9rtI2AGGHA.2012@TK2MSFTNGP14.phx.gbl...
> Prospero,
>
> If I understand your spec correct, there is no easy way to do this.
>
> But even the query you proposed will not work.
>
>
> This will give you the error
>
> Server: Msg 116, Level 16, State 1, Line 1
> Only one expression can be specified in the select list when the subquery
> is not introduced with EXISTS.
>
> If you change the query to only return city, still you will get the error.
>
> Server: Msg 512, Level 16, State 1, Line 1
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
>
> If you change the query using IN as
>
>
> It will return all the rows back.
>
> You can rewrite the quesry as following
>
>
>
> But even thats is not guaranteed to give you the correct results, because
> the
> query will list all employees who's salary is equal to the max salary of
> ANY city.
>
> So to achieve the desired result, the query should be
>
> SELECT Lastname,city,Salary

> FROM Employees O
> WHERE HireDate = (SELECT Max(Salary) FROM Employees I
> WHERE I.City = O.City)
>
>
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "Prospero via webservertalk.com" <u16785@uwe> wrote in message
> news:5a4791606a752@u
we...
>
>

Andrey Odegov

2006-01-13, 7:23 am

Here is the solution which is more correct:

SELECT P1.*
FROM Table AS P1
JOIN Table AS P2
ON P2.name <> P1.name
AND P2.city = P1.city
AND P2.salary >= P1.salary
GROUP BY P1.employeeID, P1.name, P1.city, P1.salary
HAVING COUNT(*) = 1;

---
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)

Andrey Odegov

2006-01-13, 7:23 am

Pardon me
i seem to get tired
this is the last version

SELECT P1.*
FROM Table AS P1
WHERE NOT EXISTS(SELECT *
FROM Table AS P2
WHERE P2.city = P1.city
AND P2.salary > P1.salary);

---
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)

Sponsored Links