postgresql - Get repeated values -
i have table this:
i need numbers, wich repeated in different citys , these citys quantity each number.
that table, need result:
number | repeated citys quantity ---------- 222 | 2
because number 222
repeated in 2 different city.
i have solution:
1) greate function returns unique values array, example array_unique()
2) , then:
select number, array_length(uniq_city_list, 1) ( select number, array_unique(array_agg(city)) uniq_city_list mytable group number ) t array_length(uniq_city_list, 1) > 1
but, may there better solution doing this? think not optimal query...
select number, count(*) ( select number, city t group number, city ) s group number having count(*) > 1 order number
Comments
Post a Comment