Select rows with first row of a related table

[iaresean]

Hi All;

Been a while since I have visited Tek-Tips, but I small SQL problem that has been bothering me.

I need to select a set from a table and include the first row of a related set of data from another table.

Does anyone know of an elegant statement that SQL Server 2005 may provide to solve this requirement?

My thanks in advance! :-D

Sean.

 

[r937]

tell us about the tables

also, what do you mean by "first" row?

r937.com | rudy.ca

 

[iaresean]

Hi r937;

Well just about any 2 tables with a 1-to-many relationship will do.

Say for arguments sake I have a table of People, and another table of Jobs. I want to select all the people from the people table along with the first job that they did.

Thanks again;

Sean.

 

[r937]

okay, "the first job they did" implies a date column, yes?

select persons.name
     , jobs.title
     , jobs.datestarted
  from persons
inner
  join jobs
    on jobs.person_id = persons.id
   and jobs.datestarted =
       ( select min(datestarted)
           from jobs
          where person_id = persons.id )

r937.com | rudy.ca