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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s