PostgreSQL Data Analysis / Aggregates

Question

I have a table in PostgreSQL with the following structure & data:

Question      | Answer      | Responses
---------------------------------------
Burger          BigMac        8
Burger          Whopper       19
Burger          Cheeseburger  4
Drink           Coke          22
Drink           Water         1
Drink           Juice         7
Side            Salad         8
Side            Fries         19

How can I run a query that returns the 'Answer' with the higest 'Responses' for each 'Question'? For the above data I'd want to see something like:

Question      | Answer      | Responses
---------------------------------------
Burger          Whopper       19
Drink           Coke          22
Side            Fries         19

I don't have any problems getting the higest 'Response' foreach 'Question', but also pulling out the relevant 'Answer' is proving to be a problem. The SQL that works to get the Question & highest response is:

SELECT Question, MAX(Responses) FROM mytable GROUP BY Question;

Can anybody shed any light on the last part of my equation - showing the relevant Answer?

I have tried this:

SELECT Question, Answer, MAX(Responses) FROM mytable GROUP BY Question;

however Postgres complains that Answer isn't being used in an aggregate or GROUP BY statement. Do I just need to determine all my questions beforehand, then do a SQL query for each question to find the answer with the most responses? I'd rather not go down this messy path, but it's an option I guess.

Thanks!

Answer 1

SELECT
    DISTINCT ON (question)
    question, answer, responses
FROM
    mytable
ORDER BY
    question, responses DESC;

Answer 2

One standard way to do this is using window functions. Unfortunately this requires 8.4, but if you can try that, something like this should work:

SELECT question, answer, responses
FROM (
  SELECT question,answer,responses,row_number()
    OVER (PARTITION BY question ORDER BY responses DESC)
  FROM mytable
) AS t 
WHERE row_number=1