Filter record which matches - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Filter record which matches

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-12 : 16:29:55

Hi
I have a table where i have some records which have duplicate values based on some fields. Lets Say
LastName,FirstName,Address,Zip,State,Country

So I want to filter The result if firtname,lastname and address of one row is equal to firstname,lastname and address of another row.

Thnx in adv

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-03-12 : 16:45:14

When you say filter, do you mean exclude? Do you just want to see the duplicates once? Can you provide more detail.

Rome was not built overnight.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-12 : 17:46:54

Just group them together:

SELECT LastName, FirstName, Address
FROM Table1
GROUP BY LastName, FirstName, Adress

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-12 : 18:41:55

Thnx
Yes I want to exclude duplicate entries but i want all field, so if i use group by clause then it will only give me the fields which are in group by.

Ex: Select firstname,lastname,address,zip,state from table1 group bt firstname,lastname,address
which is a wrong sql query

Thnks again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-12 : 18:47:04

How to do want to return the ZIP and State columns w/o grouping by them? Either you group by them as well, exclude them, or summarize them somehow (i.e., min or max). Give us an example of your data and what you'd like to return.

- Jeff

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-12 : 19:07:03

Thnx
if I have 10 fields in my table and i want to show all of them but i want to group only 3 fields, then i cant use TARA's solution. what I want here is a solution to match some fields which have same data and if data matches on that specific fields then exclude that entire row, which is a duplicate row.

So lets say if lastname,fistname and address have same values in many rows , show only one row but also wants to show all(*) fields of table

Thnx Again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-12 : 19:27:19

How do you know which Address or Zip code to show? Take this for example:

Last name: Duggan
First name: Tara
Address: 1234 Main Street

Now for first row, I have zip code as 92121. For second row I have zip code as 92105. Which zip code do you want to show?

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-12 : 19:42:02

tHank
Here i got ur point.
Let me work on it and i let u know caz I have one issue which is i want to match left(address,10) but want to show full address in select statement

like for eg:
SELECT S.firstName,S.LASTNAME,S.Address1 FROM table1 S group by
S.firstName,S.LASTNAME,left(S.Address1,8)
which is wrong statement

Thnx again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-12 : 21:11:55

Please post sample data and show us exactly what you want in your result set using the sample data. It'll make it a lot easier for us to come up with a solution.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-12 : 21:55:07

Thnx Tara
Now u can see one possible case. In this case i have one client with value in different fields. in address1 (street,st) and in company (Charl's Inc,Charls Inc).
FirstName,LastName,Address1,Address2,City,State,Zip,CompanyName
John,mike,12 keele street,null,abc,CA,12345,Charl's Inc.
John,mike,12 keele st,null,abc,CA,12345,Charl's Inc.
John,mike,12 keele street,null,abc,CA,12345,Charls Inc.

Thnx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources