KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
I have a table like this: Column | Type | Modifiers ---------+------+----------- country | text | food_id | int | eaten | date | And for each country, I want to get the food that is eaten most often. The best I can think of (I'm using postgres) is: CREATE TEMP TABLE counts AS SELECT country, food_id, count(*) as count FROM munch GROUP BY country, food_id; CREATE TEMP TABLE max_counts AS SELECT country, max(count) as max_count FROM counts GROUP BY country; SELECT country, max(food_id) FROM counts WHERE (country, count) IN (SELECT * from max_counts) GROUP BY country; In that last statement, the GROUP BY and max() are needed to break ties, where two different foods have the same count. This seems like a lot of work for something conceptually simple. Is there a more straight forward way to do it?
Tags (comma-separated)
Save Edits
Cancel