no duplicates please - 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 2005 Forums

Transact-SQL (2005)

no duplicates please

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-03-10 : 16:17:14

Hi, All,

I have two columns of int data in the a table, as my example data shows below.

I want my data returned to be something like those in #test3, but my question is this, how can I do it without using #test2 and #test3?

By the way, the business requirement doesn't care it's min/max or any ID when one side has duplicated values.

Thanks!

Use tempdb
Go

if object_ID ('#test') is not null drop table #test

create table #test (col1 int, col2 int)
insert into #test
Select 123, 222 union
Select 124, 222 union
Select 125, 222 union
Select 111, 223 union
Select 111, 224

if object_ID ('#test2') is not null drop table #test2
create table #test2 (col1 int, col2 int)
Insert into #test2
Select distinct col1, min(col2) from #test group by col1

if object_ID ('#test3') is not null drop table #test3
create table #test3 (col1 int, col2 int)
Insert into #test3
Select min(col1), col2 from #test2 group by col2

Select * from #test3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 16:27:59

Try this:


select min(t.col1) as col1, t.col2
from #test t
inner join
(
	select col1, min(col2) as col2
	from #test
	group by col1
) d
on t.col1 = d.col1 and t.col2 = d.col2
group by t.col2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-03-10 : 16:38:18

Wow, thank you!

I had a mental block on the last line when I tried on my own earlier:

group by t.col2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources