Distinct or GroupBy - 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

General SQL Server Forums

New to SQL Server Programming

Distinct or GroupBy

Author

Topic

osullivj
Starting Member

4 Posts

Posted - 2011-06-17 : 11:20:48

Ok..This should be easy but I cant figure it, I have the following table

ID,Name,Address
1,Tom,Main Street
2,Tom,Main Street
3,Tom,Main Street
4,Tim,Main Street
5,Jane,Main Street
6,Jane,Main Street

I would like to get back only the unique records based on name and address taking the first id i.e.

1,Tom,Main Street
4,Tim,Main Street
5,Jane,Main Street

Any ideas?

As our society gets more complex and our people get more complacent, the role of the jester is more vital than ever before. Please stop sitting around. We need you to make a ruckus.
Seth Godin - Linchpin

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 11:26:54

selecy id = min(id), name, address
from tbl
group by name, address

select id, name, address from
(
select *, seq = row_number() over (partition by name, address order by id)
from tbl
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

osullivj
Starting Member

4 Posts

Posted - 2011-06-17 : 12:32:40

OK What if I have a value column that I need to take into account

ID,Name,Address,Amount
1,Tom,Main Street,1
2,Tom,Main Street,4
3,Tom,Main Street,1
4,Tim,Main Street,2
5,Jane,Main Street,3
6,Jane,Main Street,3

I would like to get back only the unique records based on name and address and value taking the correct id i.e.

2,Tom,Main Street,4
4,Tim,Main Street,2
5,Jane,Main Street,3

As our society gets more complex and our people get more complacent, the role of the jester is more vital than ever before. Please stop sitting around. We need you to make a ruckus.
Seth Godin - Linchpin

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 12:36:37

select id, name, address, value
from
(
select *, seq = row_number() over (partition by name, address order by value desc, id)
from tbl
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources