MAX(dam, it just don't work right) query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MAX(dam, it just don't work right) query

Author

Topic

Osangar
Starting Member

7 Posts

Posted - 2005-04-06 : 09:00:51

Hi chaps,

I was hoping someone could help me with this query please

I need to find the maximum salaries of male and female staff.
I have a staff table with staff salaries in one column and another column with staff sex.

I have tried the following, but it does not work and I cant seem to get my brain around what seems like a simple query.

Select distinct
male Staff Salary = (select MAX(salary)where staff.sex = 'M')
female Staff Salary = (select MAX(salary)where staff.sex = 'F')
from staff

any assistance would be greatly apreciated

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-06 : 09:05:33

try something like this:


select
  MAX(Case when sex = 'M' then Salary else 0 End) as MaxMaleSalary,
  MAX(Case when sex = 'F' then Salary else 0 End) as MaxFemaleSalary,
  MAX(Case when sex = 'Not Sure' then Salary else 0 End) as MaxNotSureSalary 
from
  YourTable

by the way, when you ask a question and say "something doesn't work" it is important to say what you mean by that .. does it return errors? does it return wrong data? does it even compile? etc... It's like going to a mechanic and saying "my car doesn't work .. how much will it take to fix it?"

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-06 : 09:08:02

[code]
select m_salary = (select max(salary) from staff where sex = 'm'),
f_salary = (select max(salary) from staff where sex = 'f')
[/code]

EDIT: yeah! what Dr. said!!

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-06 : 09:13:48

spirit, you forgot the 3rd possibility !

- Jeff

Osangar
Starting Member

7 Posts

Posted - 2005-04-06 : 09:15:18

That has worked a treat; I really appreciate your assistance spirit1.

Ahh yes sorry jsmith8858 that is a good point.

Cheers, for both the code and guidance.

Have a great day

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-06 : 15:07:43

This will take care of all the possibilities, wouldn't it? :)

Select sex, max(salary) as maxsalary from YourTable group by sex

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-06 : 15:08:58

depends how you want the results to be returned, in multiple rows or multiple columns.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-06 : 15:14:00

Yeah, I just wanted to make sure Osanger realized it was a possiblility. (plus anytime I can type "group sex" is just a bonus)

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-06 : 15:15:36

ROTFL!!!!!!