SQL find the 2 highest score for each country-CodePudding

I have two tables: maps_query and map_time like below:

CREATE TABLE maps_query (
    id             int 
    day            varchar
    search_query   varchar
    country        varchar
    query_score    int
)

CREATE TABLE map_time (
    id   int
    am_pm  varchar
)

The question is to find the 2 highest score for each country. Desired output is like below:

country  search_query  query_score
CA        Target       1000
CA        Store        900
FR        Eiffiel      1500
FR        London       800

I was trying to use row_number() over but don't know how to complete the query.

Select t1.country, t1.search_query, t1.score_rank, 
from (select *, (row_number() over(partition by country order by query_score) as score_rank from maps_search) t1
where score_rank = 1 and score_rank=2

CodePudding user response：

This can be achieved by rank() instead of row_number().

select 
  * 
from 
  (
    select 
      *, 
      rank() over (
        PARTITION by country 
        order by 
          query_score desc
      ) 
    from 
      maps_query
  ) q 
where 
  rank <= 2;

A good reference article: https://spin.atomicobject.com/2016/03/12/select-top-n-per-group-postgresql/