August 8, 2012 at 8:20 am
I have an Address Table, Here are all the Fields
PriKey,Key2,Key3,Key4,Key5,SortPos,AddressType,Attn,Addr1,Addr2,City,State,ZipCd,Status,BegSrvDt,Notes,EnterDt,EnterBy
So all my clients and vendors addresses are in this table. Most of my clients have multiple addresses. Usually I just filter by
client or vendor, then display all the associated addresses. However, showing all of 1 clients addresses is starting to become an issue.
When adding a new address, you may have to scroll through 5 different addresses because each of the 5 addresses is listed 5,6,7 times,
giving a list of 30 or more addreses. We have good reason for duplication but now I need to filter unique addresses. So, I make the query:
SELECT DISTINCT Addr1 FROM Address WHERE AddressType='Ship' AND Key2=10101
I assign that to my list for Adding New Addresses, and everything is great.
So, now my problem is that I need to return results with All the table fields. The above query just gives me the Distinct columns I specify.
I need to do something like this:
SELECT * FROM Address WHERE EXISTS(SELECT DISTINCT Addr1 FROM Address WHERE AddressType='Ship' AND Key2=10101)
My second query gives me every record in the table. What I need is all columns and all rows where AddressType='Ship' AND Key2=10101 AND
the unique value of Addr1. If there are 3 rows where AddressType='Ship' AND Key2=10101 AND Addr1='55 Main St.', the results should just
display 1 row.
August 8, 2012 at 8:56 am
create table #address(a varchar(50),b int)
insert into #address values ('pat',1),('giyo',2)
insert into #address values ('pat',1),('giyo',2)
select * from #address
this should do the work
with cte as (
select *,row_number() over(partition by a order by a asc) address_id from #address
)
select * from cte where address_id=1
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
August 8, 2012 at 9:40 am
Thank You !!! Apparently its time to update myself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply