select only first row 2

[jamor1999]

Hi everyone,

I'm new to SQL and need some help. I have a table that includes customer number (unique) and one job number per customer. I have another table that includes customer number (not unique) and several job numbers (unique) and their locations (not unique) for each customer.

*Note: these tables are imported from an outside application that uses a proprietary database (flat file).

Here's a sample layout:

Table ar1:
CustomerNumber | JobNum
10003000 | 235
20070000 | 496
02589888 | 125

Table jc1
CustomerNumber | JobNum | JobLocation
10003000 | 235 | 3 Tea Lane
10003000 | 555 | 3 Tea Lane
10003000 | 668 | 3 Tea Lane
20070000 | 496 | 555 5th Street
20070000 | 147 | 555 5th Street


I needed to pull all customerNumbers from table ar1 but only the Location from the first corresponding row from table jc1.

For example, if I were to Select CustomerNumber 10003000 from ar1 and the job location of the 'first corresponding row' in jc1 for this customer number I would see:

CustomerNumber | Location
10003000 | 3 Tea Lane

I hope I explained this clearly enough.

Can anyone help??

 

[nigelrivett]

>> 'first corresponding row'
What do you mean by this - that with the min jobno?
A table has no intrinsic order so you neeed some column to order by.

select t1.CustomerNumber, t2.location
from ar1 t1
join jc1 t2
on t1.CustomerNumber = t2.CustomerNumber
where t2.JobNum = (select min(t3.JobNum) from jc1 t3 where t3.CustomerNumber = t2.CustomerNumber)

======================================
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.

 

[vnad]

Use Books online and look up the TOP command. Should give you want you want.

Can give you the TOP number of rows or a percent.

 

[jamor1999]

Thanks for the ideas! Yes min or max would probably give me what I want (I hadn't thought of that!).

I like the sound of that Top function. I'll look into it! You're my heroes!