CODEWITHSUNDEEP
sqlamazon-redshift
Brad Davis
Brad Davis

Reputation: 1170

Select top three records grouping by two factors

I am trying to identify the three records with the highest values grouped by two factors. I realize this question is similar to this one PostgreSQL: select top three in each group, but I can't figure out how to generalize from this example which includes a single factor, to two factors. I have tried searching stack overflow for an answer to this question beyond the one listed above and I can't find one, but perhaps I'm not searching for the correct terms.

Briefly, I'm connecting to a table with the following schema

city, country, value

I only have a single row per city, country combination, but I have a variable, but the number of city entries I have per country is variable. For example, I have a few dozen cities for Canada, a hundred for the United States, but only two for Uzbekistan.

What I want, as output is a table with the same schema, but only countaining the rows containing the highest three values for city, nested within country. For example, if Canada has the cities and values of 

{Canada, toronto, 100},     {Canada, vancouver, 80}, 
{Canada, montreal,112},     {Canada, calgary, 109}, 
{Canada, edmonton, 76},     {Canada, winnipeg, 73},

and the United States has the entries of

{{us, nyc, 104},     {us, chicago, 87}, 
{us, boston, 98},    {us, seattle, 105}, 
{us, sanfran, 88},   {us, minneapolis, 84}, 
{us, miami, 103},    {us, houston, 112}, 
{us, dallas, 78},    {us, tucson, 83}}

and Uzbekistan has the entries of

 {uzbekistan, qarshi, 95}, {uzbeckistan, gluiston, 101}

What I would like as output would be

Canada, Montreal, 112
Canada, Toronto, 100
Canada, Calgary, 109
us, houston, 112
us, seattle, 105
us, nyc, 103,
uzbeckistan, qarshi, 95
uzbeckistan, gluiston 101

I've tried the following query

 SELECT logincity, logincountry, VAL
 FROM
 (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY logincountry, logincity ORDER BY 
 val DESC) AS Row_ID
 FROM a_table)
 WHERE Row_ID < 4 
 ORDER BY logincity

But I end up with more than three cities per country. Can someone help me out?

Thanks Stack Overflow!

Upvotes: 0

Views: 56

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

I think you only need partition by logincountry

SELECT logincity, logincountry, VAL
FROM (
     SELECT *, 
            ROW_NUMBER() OVER (PARTITION BY logincountry 
                               ORDER BY val DESC) AS Row_ID
     FROM a_table ) T
WHERE Row_ID < 4 
ORDER BY logincity

TIP: You probably will realize the problem if you include the Row_id on the SELECT

SELECT logincity, logincountry, VAL, Row_ID

On your query all Row_ID = 1

TIP 2: Your query want top 3 cities for each country, so you only have one partition country. So the linked question is the right answer, top 3 of each group in this case country.

Upvotes: 1

Related Questions