Select and highest number in row?

[Anumet]

I have a table with the following fields:

gender (male/female)
race (human/orc)
class (hunter/rogue/warrior)
Str
Int
Wil
Dex
Con
Statsum

str, int, wil, dex, con and sum are all numbers.

What I want my query to get hold of is all homelands where at least one of the entries has a str above 16, at least one entry has a dex above 17 and at least one entry that has a con above 15.

I started off with this:

SELECT DISTINCT homeland FROM Stats
WHERE gender = 'male' AND race = 'human' AND statclass = 'hunter' AND (str >= '16' OR dex >= '17' OR con >= '15');

But this of course gives me any homeland that has EITHER high enough str dex or con - how do I get only those who have at least one entry with the wanted number or higher?

Thanks!

Anumet

 

[TonyGroves]

What do you mean by "homeland"?


-----
ALTER world DROP injustice, ADD peace;

 

[Anumet]

soz. homeland is also part of the table. Homeland can be f.ex. Gondor Rohan or Mordor.

 

[TonyGroves]

How about:
[tt]
SELECT homeland,MAX(str) s,MAX(dex) d,MAX(con) c
FROM stats
WHERE
gender='male'
AND race='human'
AND statclass='hunter'
GROUP BY homeland HAVING s>16 AND d>17 AND c>15
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Sorry, that's wrong! Try instead:
[tt]
SELECT homeland,MAX(str>16 AND dex>17 AND con>15) c
FROM stats
WHERE
gender='male'
AND race='human'
AND statclass='hunter'
GROUP BY homeland HAVING c=1
[/tt]


-----
ALTER world DROP injustice, ADD peace;