quote:

---

Originally posted by spowell

Hi, I am really new to sql still and need a bit of help please. I am sure this is a simple problem but just not simple enough for me!!

I want to send a letter to all the addresses in my database but some addresses have 2 different customers living there. I only want to send out 1 letter to each address. The query I have come up with removes the duplicates if the clients name is the same but I can't get it to work properly if there are 2 different names at the same address.

select title, firstname, surname, address1, address2, address3, county, postcode
from addresses
group by title, firstname, surname, address1, address2, address3, county, postcode
order by postcode desc, address1


Any help would be greatly appreciated

---

select a.title, a.firstname, a.surname, a.address1, a.address2, a.address3, a.county, a.postcode
from addresses a
inner join(
select max(pk) as maxrec,address1, address2, address3, county, postcode
from addresses
group by address1, address2, address3, county, postcode)a1
on a1.address1=a.address1
and a1.address2=a.address2
and a1.address3=a.address3
and a1.county=a.county
and a1.postcode=a.postcode
and a1.maxrec=a.pk
order by a.postcode desc, a.address1

quote:

---

Originally posted by spowell

Thanks a lot for the reply but when I run the query it does execute successfully but I get 0 rows as the result.

Is there anything else I can try or have I done something wrong? Below is the query I have run

select a.title, a.firstname, a.surname, a.address1, a.address2, a.address3, a.county, a.postcode
from addresses a
inner join(
select max(postcode) as maxrec,address1, address2, address3, county, postcodefrom addresses
group by address1, address2, address3, county, postcode)a1
on a1.address1=a.address1
and a1.address2=a.address2
and a1.address3=a.address3
and a1.county=a.county
and a1.postcode=a.postcode
and a1.maxrec=a.postcode
order by a.postcode desc, a.address1

---

quote:

---

Originally posted by spowell

sorry I made a mistake with the posting. id2 is my primary key and it should of read

select a.title, a.firstname, a.surname, a.address1, a.address2, a.address3, a.county, a.postcode
from addresses a
inner join(
select max(id2) as maxrec,address1, address2, address3, county, postcode
from addresses
group by address1, address2, address3, county, postcode)a1
on a1.address1=a.address1
and a1.address2=a.address2
and a1.address3=a.address3
and a1.county=a.county
and a1.postcode=a.postcode
and a1.maxrec=a.id2
order by a.postcode desc, a.address1

---

select		title,
		firstname,
		surname,
		address1,
		address2,
		address3,
		county,
		postcode
from		(
			select	title,
				firstname,
				surname,
				address1,
				address2,
				address3,
				county,
				postcode,
				row_number() OVER (PArtition by address1, address2, address3, county, postcode order by title) AS recid,
				count(*) OVER (PArtition by address1, address2, address3, county, postcode) AS items
			from	addresses
		) AS d
where		recid = 1
		and items > 1
order by	postcode desc,
		address1

quote:

---

Originally posted by Peso

Visakh, your code will also get all customers that live alone on the address, since you have no HAVING part in the derived table.
You have not checked for multiple customers on same address.

Spowell, see this

select		title,
		firstname,
		surname,
		address1,
		address2,
		address3,
		county,
		postcode
from		(
			select	title,
				firstname,
				surname,
				address1,
				address2,
				address3,
				county,
				postcode,
				row_number() OVER (PArtition by address1, address2, address3, county, postcode order by title) AS recid,
				count(*) OVER (PArtition by address1, address2, address3, county, postcode) AS items
			from	addresses
		) AS d
where		recid = 1
		and items > 1
order by	postcode desc,
		address1

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

You seem to be right.
I interpreted the assignment that he wanted to send out a letter to one customer for each address where the address have more than 1 customer.

If you are right, just delete the Item lines (2) in ny suggestion.

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

NULLs, maybe?
JOINing on NULLs is not a good idea.

ROW_NUMBER() does not have that problem.

---

E 12°55'05.63"
N 56°04'39.26"

---

select		a.title,
		a.firstname,
		a.surname,
		a.address1,
		a.address2,
		a.address3,
		a.county,
		a.postcode
from		addresses as a
inner join	(
			select		max(id2) as maxrec
			from		addresses
			group by	address1,
					address2,
					address3,
					county,
					postcode
		) as a1 on a1.maxrec = a.id2
order by	a.postcode desc,
		a.address1

select		title,
		firstname,
		surname,
		address1,
		address2,
		address3,
		county,
		postcode
from		(
			select	title,
				firstname,
				surname,
				address1,
				address2,
				address3,
				county,
				postcode,
				row_number() OVER (PArtition by address1, address2, address3, county, postcode order by title) AS recid
			from	addresses
		) AS d
where		recid = 1
order by	postcode desc,
		address1

quote:

---

Originally posted by spowell

Thanks to both of you for all of your help. Have managed to get it to work now with the 1st option of your last post Peso. I did try the 2nd option as well but got the following error

Msg 195, Level 15, State 10, Line 18
'row_number' is not a recognized function name.

Not sure if that was just me making a mistake though

Thanks again

---