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
Post a Comment