Help with aggregate SQL

Hello everyone,
I'm thinking this must be relatively simple, but my brain is not cooperating. Let's say I had a table like this...

FirstName | LastName | Sex | State
----------|----------|-----|------
Jim       | Beam     | F   | AK
Buddy     | Hackett  | F   | AK
Janet     | Reno     | M   | AK
Sexy      | Sally    | M   | NY

For each state, I want to know if there are more males or more females.... and for my query, the only column I can have in my "GROUP BY" clause is "STATE". So I need something along these lines (though obviously incorrect)....

SELECT STATE, MAX(SEX) AS MAJORITY_SEX FROM MY_TABLE GROUP BY STATE I would like to see a query result of...

STATE | MAJORITY_SEX
------|-------------
AK    | F
NY    | M

I'm thinking I can somehow do this with some of Oracle's aggregate functions, but I'm not seeing it. Also, please keep in mind, that I'm not looking for a solution for this particular example (I just made it up for illustration)... I'm looking for how to make queries that figure out the maximum frequency of distinct values in a column, grouped by values of another column.

Any ideas?

Thanks!!! Received on Tue Aug 06 2002 - 23:28:34 CDT