Join on First Match Only - 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)

Join on First Match Only

Author

Topic

Jazzinfour
Starting Member

3 Posts

Posted - 2011-05-15 : 23:43:12

I have been assigned a task to create a select statement to return data from two tables like these:

Table1 : Name, Rank, Value
Bob 12 121
Bob 15 121
Fred 10 132
Fred 11 132
Fred 16 132

Table2 : Name, Value, Position
Bob 121 Manager
Fred 132 Assistant

The desired result would be:

Result : Name, Rank, Value, Position
Bob 12 121 Manager
Bob 15 121
Fred 10 132 Assistant
Fred 11 132
Fred 16 132

Currently I can only get this with an inner join:

Result : Name, Rank, Value, Position
Bob 12 121 Manager
Bob 15 121 Manager
Fred 10 132 Assistant
Fred 11 132 Assistant
Fred 16 132 Assistant

How can I return either a null or blank for every row
but the first?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-15 : 23:50:08

that is more of a presentation issue. You should do this in your front end application

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-16 : 09:41:18

This is known as Suppress if duplicated feature that should be done in the front end application

Madhivanan

Failing to plan is Planning to fail

Jazzinfour
Starting Member

3 Posts

Posted - 2011-05-16 : 10:05:45

Yes, yes, I know, I would normally do this in the front-end but in this case I do not have access to source code for the front end, only an available field to use for the column data. If this were a simple report or .Net or VB.... I'd have it solved already.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-16 : 10:16:21

Post your full code

Madhivanan

Failing to plan is Planning to fail

MSquared
Yak Posting Veteran

52 Posts

Posted - 2011-05-16 : 10:30:22

Try this

declare @Table1 table(Name varchar(10), Ranking tinyint, Value tinyint)

insert into @Table1
select 'Bob',12, 121 union all
select 'Bob', 15, 121 union all
select 'Fred', 10, 132 union all
select 'Fred', 11, 132 union all
select 'Fred', 16, 132

declare @Table2 table(Name varchar(10), Value tinyint, Position varchar(15))
insert into @Table2
select 'Bob', 121, 'Manager' union all
select 'Fred', 132, 'Assistant'

;with cte as
(select a.name, a.ranking, a.value, b.Position,
ROW_NUMBER() over (PARTITION by a.value order by a.value, a.Ranking) RowNum
from @Table1 a inner join @Table2 b on a.value = b.value)

select Name, Ranking, Value,
case when RowNum > 1 then '' else Position end Position
from cte

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Jazzinfour
Starting Member

3 Posts

Posted - 2011-05-16 : 12:41:29

MSquared,

That's the ticket! Much appreciated.

MSquared
Yak Posting Veteran

52 Posts

Posted - 2011-05-16 : 14:16:20

Glad that worked for you, but keep in mind, this sort of thing should really be done in the presentation tier and not in the database as the others have stated

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources