Selecting only the top 1 from an inner join

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 2000 Forums

Transact-SQL (2000)

Selecting only the top 1 from an inner join

Author

Topic

DaCheese
Starting Member

3 Posts

Posted - 2002-11-20 : 06:33:26

Ok this seams like an extremely simple SELECT query, but no amounts of TOPs and DISTINCTS and GROUP BYs seem to do it, so I'm wondering if you could put me out of my misery;

I have 2 tables, lets say

TableOne
---------------
ONE_ID
ONE_Name

TableTwo
---------------
TWO_ID
TWO_OneID
TWO_Name
TWO_Rank

So basically each record in TableTwo has a foreign key link )TWO_OneID) thingamajig to TableOne. Multiple records in TableTwo can link to one record in TableOne (natch).

What I want to do is SELECT all the records in TableOne that have at least one TableTwo record - but only return the top 1 table 2 record.

See if I do;
SELECT DISTINCT TableOne.ONE_ID, TableOne.ONE_Name, TableTwo.TWO_ FROM TableOne INNER JOIN TableTwo ON TableOne.ONE_ID = TableTwo.TWO_OneID ORDER BY TableOne.ONE_ID, TableTwo.TWO_Rank

I get
ONE_ID TWO_ID TWO_Rank
-----------------------
1 1 100
1 2 97
1 3 91
2 4 99
2 6 81
3 5 99

Where what I want to get is;

ONE_ID TWO_ID TWO_Rank
-----------------------
1 1 100
2 4 99
3 5 99

Hope that makes sense.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-20 : 07:17:56

select ONE_ID, TWO_ID, TWO_Rank
from TableOne , Table2
where TWO_ID = (select top 1 TWO_ID from table2 where TWO_OneID = ONE_ID order by TWO_Rank desc) as a
and TWO_OneID = ONE_ID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DaCheese
Starting Member

3 Posts

Posted - 2002-11-20 : 08:36:10

beautiful, thanks a lot :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources