The solution describes itself. For extra bonus points, stop where it says 'STOP HERE' and try and figure it out.
--task:
--given a customer table with 3 lines and an address table with 6 lines,
--write a query to return one line per customer and only the first address.
declare @Customer table (custnmbr varchar(15), custname varchar(50) , rowid int identity)
declare @Address table (custnmbr varchar(15), address1 varchar(20), city varchar(20), rowid int identity)
insert into @Customer (custnmbr, custname) values ('Able', 'Able Technology')
insert into @Customer (custnmbr, custname) values ('Baker', 'Baker Delivery')
insert into @Customer (custnmbr, custname) values ('Charlie', 'Charlie Bakery')
insert into @Address (custnmbr, address1, city) values ('Able', '123 Main', 'Reno')
insert into @Address (custnmbr, address1, city) values ('Able', '123 Oak', 'Las Vegas')
insert into @Address (custnmbr, address1, city) values ('Baker', '123 Persimmon', 'Buffalo')
insert into @Address (custnmbr, address1, city) values ('Baker', '123 Apple', 'Rochester')
insert into @Address (custnmbr, address1, city) values ('Charlie', '123 Elm', 'Tampa')
insert into @Address (custnmbr, address1, city) values ('Charlie', '123 Walnut', 'Miami')
--this query returns six lines, because each city has two addresses
select *
from @Customer c
join @Address a on c.custnmbr = a.custnmbr
--STOP HERE, and see if you can come up with the solution on your own.
--solution:
--this query adds a row number to the address table, and it restarts for each customer number
--so, we get 1,2 for each customer.
--run this, and be sure you understand what the ROW_NUMBER() function is doing.
select a.custnmbr, a.address1, a.city,
ROW_NUMBER() over (partition by a.custnmbr order by a.custnmbr) as RowNumber
from @Address a
--now, we just join to the address rows that have a '1' and we'll only get one address
select *
from @Customer c
join (
--this is a 'derived table', it acts just like a table and can be joined like a table
--it's an exact copy of the query above
select a.custnmbr, a.address1, a.city,
ROW_NUMBER() over (partition by a.custnmbr order by a.custnmbr) as RowNumber
from @Address a
) a on a.custnmbr = c.custnmbr and a.RowNumber = 1