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
Advertisement
Answer
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/