By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,262 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

Display non-distinct records in sql

P: 5
Hi,
I have a table with many columns but am concerned with only 2 columns - vin_no and address. I have repeating address and different vin nos. , also repeating address and same vin nos.

I want the following:
Display repeating addresses with different vin nos ONLY. It should not display records that have same vin nos.

How is this possible?

Thanks.
Mar 14 '08 #1
Share this Question
Share on Google+
5 Replies


MindBender77
100+
P: 234
I want the following:
Display repeating addresses with different vin nos ONLY. It should not display records that have same vin nos.

How is this possible?

Thanks.
You could try a find duplicate query. Some like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.address, Table1.Vin
  2. FROM Table1
  3. WHERE (((Table1.address) In (SELECT [address] FROM [Table1] As Tmp GROUP BY [address] HAVING Count(*)>1 )))
  4. ORDER BY Table1.address;
  5.  
Hope this Helps,
Bender
Mar 14 '08 #2

P: 5
This helped, I think!!
Thanks a lot!!!
Mar 17 '08 #3

P: 5
Hi,
I looked at the result but it looks like it is also displaying the duplicate records, meaning same vin_no with same address appears more than once....can we eliminate duplicates?

also we need to add a condition where sender_id = 'GMCA'

Thanks.
Mar 18 '08 #4

P: 5
please help...it is not displaying the correct result.
Mar 19 '08 #5

MindBender77
100+
P: 234
Hi,
I looked at the result but it looks like it is also displaying the duplicate records, meaning same vin_no with same address appears more than once....can we eliminate duplicates?

also we need to add a condition where sender_id = 'GMCA'

Thanks.
Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.address, Table1.Vin
  2. FROM Table1
  3. WHERE (((Table1.address) In (SELECT [address] FROM [Table1] As Tmp GROUP BY [address] HAVING Count(*)=1 )) AND ((Table1.sender_id)="GMCA"))
  4. ORDER BY Table1.address;
  5.  
Bender
Mar 19 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.