Selecting the first record from grouped records

I need to create a table of localities with the number of waste bins associated with each, however, the address table can have multiple localities for a single property number. Therefore, rather than skew the results by counting a single bin twice (or more) for each locality the property has an address in it is better to simply select the first locality in the list of address and assign that to the bin count.

For example:

select b.property_address_2 as locality,

sum (a.number) as bin_count

from rd_waste as a

join

(select distinct property_number,

property_address_2,

row_number() over (partition by property_number order by property_address_2) as record 

from rd_address) as b

on a.property_number = b.property_number

where a.description like ‘%Waste%’

and b.record = 1

group by b.property_address_2

order by b.property_address_2

Advertisements