Top 1 on Join statement - 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

Top 1 on Join statement

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2011-05-04 : 08:54:14

DECLARE @tbl_test as Table (ID INT)

DECLARE @tbl_Test1 as Table ( ID INT)

INSERT INTO @tbl_test
SELECT 1

INSERT INTO @tbl_Test1
SELECT 1
INSERT INTO @tbl_Test1
SELECT 1

How to join these two tables with top 1 from table 2

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-04 : 09:01:04

Which one is the 'top' 1?

In your example tbl_Test1 records are the exact same... so a group or distinct would give you 1 record result

Select *
From @tbl_test A
Inner Join (Select Distinct Id From @tbl_Test1) B
On A.id = B.id

Corey

I Has Returned!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 09:06:02

This will give a top 1

select t2.*
FROM @tbl_test t1
cross apply (select top 1 t.* from @tbl_test1 t where t1.id = t.id) t2

Jim

Everyday I learn something that somebody else already knew

aoriju
Posting Yak Master

156 Posts

Posted - 2011-05-04 : 09:14:46

That's i know..I have more records..How to use Top in between join

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-04 : 09:19:14

Why don't you show us some more realistic data and expected result, and we will help you figure out how to get there.

I'm not sure why you're hung up on Top, but I suspect that it may not be the answer you need.

Corey

I Has Returned!!

aoriju
Posting Yak Master

156 Posts

Posted - 2011-05-04 : 09:43:11

Cross Apply..working...Is that equal to Left join ?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 09:48:37

No, there are 2 types of APPLY -- CROSS and OUTER. You should look them up in Books On Line to get what the APPLY operator does. Basically, it treats the "left" as an input to apply to what's "inside"

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources