TSQL - Inner Join

Question

Post reply

TSQL - Inner Join

Viewing 8 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Which two do you want?

This will get you first 2 alphabetically. I assume you are using SQL 2005.

SELECT

Name, Company FROM

(SELECT Tab1.Name, Tab2.company, Row_number() OVER (Partition by Name ORDER BY Company) AS RowNo FROM Tab1 inner join Tab2 on Tab1.ID = Tab2.ID_FK) subquery

WHERE RowNo<=2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit Kumar SSChasing Mays Points: 614 More actions · Answer 2

Hi, I'm using SQL 2000. I want any two row for each Primary key.

Thanks for your effort.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

If you're using 2000, please rather post in the 2000 forums in the future. The row_number function is SQL 2005 only. Since the query was in the 2005 forum, I assumed you were using 2005

In 2000 it's a lot more difficult to do this.

You didn't answer my question. Which two rows do you want? Any two? alphabetically?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit Kumar SSChasing Mays Points: 614 More actions · Answer 4

'O' sorry. Normally i worked in SQL 2005 so wrongly i put in the 2K5 forum. For this query, i need for SQL 2000.

I need two row order by company name. Your last sent query worked in 2005. so i need in the same way.

Thanks

stewart noble SSCrazy Points: 2009 More actions · Answer 5

This works in a fairly unpleasant sort of way:

declare @id int

select @id = min(id)

from #tab1

while @id is not null

begin

insert into @tab

select top 2 a.Name,b.Company

from #tab1 a join #tab2 b on a.id = b.id_fk

where a.id = @id

select @id = min(id)

from #tab1

where id > @id

end

select * from @tab

order by name

Amit Kumar SSChasing Mays Points: 614 More actions · Answer 6

Hi

Works fine but i need the same in a single query as what u did for 2K5. If it can be done like that, will be highly appreciable. Actually i have to use in a large application. What i given u, is a small reference of that. PLz if u can

Thanks & Regards

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

This will probably work, but it is not nice. Untested. Give it a try, if it throws errors, let me know

SELECT tab1.name, min(tab2.company)

from tab1 inner join tab2 on tab1.id = tab2.id_fk

group by tab1.name

UNION ALL

SELECT tab1.name, min(tab2.company)

from tab1 inner join tab2 on tab1.id = tab2.id_fk

WHERE tab2.company != (select min(company) from tab2 t where t.id_fk = Tab1.id)

group by tab1.name

Order by 1,2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit Kumar SSChasing Mays Points: 614 More actions · Answer 8

Amit Kumar

SSChasing Mays

Points: 614

May 17, 2007 at 4:10 am

#707048

Perfect. Great man.

Thanks. Take care