A query - 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

A query

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-29 : 17:08:43

I have two results sets

Select a,b,c from table1
this result has only distinct values of column a.
and
Select a,d,e,f from table2
this result has duplicate values for column a.

I need two join the two results on the column a but eliminate the duplicates in the second table.

Select * from
(Select a,b,c from table1) r1
inner join
(Select a,d,e,f from table2)r2
on r1.a=distinct(r2.a)

How can I get distinct out of table 2.

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-29 : 18:16:52

Anybody have a solution.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-29 : 21:27:37

if you are using SQL 2005/2008, you can use the row_number() to get that.


select *
from (
         select a,d,e,f, row_no = row_number() over (partition by a order by d,e,f)
         from   table2
     ) t2
where t2.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources