DISTINCT columns but return all rows

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

SQL Server Development (2000)

DISTINCT columns but return all rows

Author

Topic

din112
Starting Member

6 Posts

Posted - 2006-04-11 : 04:39:52

Hi,

I'm having a problem with the DISTINCT statement.

What i'm trying to do is select non-duplicate values(using DISTINCT ) from an Access database.

An overview of what i'm trying to do is compare 2 addresses, prob is that addresses are the same apart from one or two columns(some minor differences), so the regular DISTINCT statement does not omit these.

But so far i can only return each distinct column, i want to be able to return all the columns depending on some distinct columns i specify.

Thanks in advance for any help,

Din

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-11 : 04:50:19

Can you post some sample data with the required output so that its get easy to understand your requirement..

you can use group by clause if you want to get the distinct data from the multiple columns..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

din112
Starting Member

6 Posts

Posted - 2006-04-11 : 05:05:16

Ok, here is some data:

[NAME] [ADDRESS1] [ADDRESS2]
[John Hin] [98 Vaughan Road] [Apartment # 108]
[Ron Lookins] [6574 East Brainerd Road]
[Igor Slabovikh] [310 E 14 Str]
[John Hin] [98 Vaughan Road,] [Apartment # 108]

Now, the John Hin row has a comma (,) in the 4th row but not in the 1st row, so SELECT DISTINCT does see it.

But what i want to do is for example, SELECT DISTINCT John Hin from the table using columns NAME and ADDRESS2, and ignore ADDRESS1.

So result would be:

[NAME] [ADDRESS1] [ADDRESS2]
[John Hin] [98 Vaughan Road,] [Apartment # 108]

Its does not matter which version of the address i am left with, just as long as its not a duplicate.

Cheers,

Din

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 05:17:45

will this do ?

select	[NAME], max([ADDRESS1]), max([ADDRESS2])
from	yourtable
group by [NAME]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-11 : 05:42:08

somthing like this ..


Declare @Tbl Table
(
[Name] Varchar(1000),
Address1 varchar(1000),
Address2 Varchar(1000)
)

Insert @Tbl 
Select 'John Hin', '98 Vaughan Road', 'Apartment # 108'
Union All 
Select 'Ron Lookins', '6574 East Brainerd Road',Null
Union All
Select 'Igor Slabovikh', '310 E 14 Str',Null
Union All
Select 'John Hin', '98 Vaughan Road,', 'Apartment # 108'


Select [Name],MAx(Address1),Max(Address2) From @Tbl Where [Name] = 
(Select Distinct [Name] From @Tbl a Where a.Address2 = Address2 And a.Address1 = Address1 )
Group by [Name]

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources