How do I join just the first matching record ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How do I join just the first matching record ?

Author

Topic

lanejc
Starting Member

11 Posts

Posted - 2002-05-19 : 00:53:01

I have two tables and I want to return all the rows from the first table but only one row from the second table per row in the first table.
For Example:

Table 1
-----------
T1id Name
1 Some User
2 Some User2

Table 2
----------
T2id T1id Value
1 1 500
2 1 300
3 1 900
4 2 1000
5 2 5000

So the result should be this:
T1id Name Value
1 Some User 500
2 Some User2 1000

How would I do this?

Nazim
A custom title

1408 Posts

Posted - 2002-05-19 : 01:00:46

select t1.t1id,t1.user,t2.value from table1 t1
inner join ( select t1id,max(value) t2id from table2
group by t1id) t2
on t1.t1id=t2.t1id

HTH

--------------------------------------------------------------

Edited by - Nazim on 05/19/2002 01:03:01

lanejc
Starting Member

11 Posts

Posted - 2002-05-19 : 01:07:09

Hmm, but I don't want the max value from the second table, I just want the first value.

I'm thinking I'm going to need to use two separate SQL statements in my code to do this.

Nazim
A custom title

1408 Posts

Posted - 2002-05-19 : 01:26:35

you can try on these lines.

select t1.t1id,t1.user,t2.value from table1 t1
inner join (

select a.t1id,a.value from table2 a
inner jon
(
select min(t2id) Tmin
from table2
group by t1id ) b
on a.t2id=b.Tmin
) k
on t1.t1id=k.t1id

--------------------------------------------------------------

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-20 : 20:41:42

quote:

Hmm, but I don't want the max value from the second table, I just want the first value.

How do you define "first"? SQL does not have an inherent ordering scheme for data in tables? Is the ID field an IDENTITY (sequential number)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources