sql - Select n largest count by category in Redshift -


i select x common pairs per group in table. let's consider following table:

+-------------+-----------+ | identifier  |    city   | +-------------+-----------+ | ab          |  seattle  | | ac          |  seattle  | | ac          |  seattle  | | ab          |  seattle  | | ad          |  seattle  | | ab          |  chicago  | | ab          |  chicago  | | ad          |  chicago  | | ad          |  chicago  | | bc          |  chicago  | +-------------+-----------+ 
  • seattle, ab occurs 2x
  • seattle, ac occurs 2x
  • seattle, ad occurs 1x
  • chicago, ab occurs 2x
  • chicago, ad occurs 2x
  • chicago, bc occurs 1x

if select 2 commons per city, result should be:

+-------------+-----------+ | identifier  |    city   | +-------------+-----------+ | ab          |  seattle  | | ac          |  seattle  | | ab          |  chicago  | | ad          |  chicago  | +-------------+-----------+ 

any appreciated. thanks, benni

you can use count in row number order number of appearances per city combination , choose first two.

select city,identifier  ( select city,identifier ,row_number() over(partition city order count(*) desc,identifier) rnum_cnt tbl group city,identifier ) t rnum_cnt<=2 

Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

.htaccess - ERR_TOO_MANY_REDIRECTS htaccess -