[RESOLVED] Finding the 'earliest' related record

I'm sure this is easy but I've got a bit of an Xmas braind drain on and can't quite seem to manage it.

I have two tables Role and TimeTableEntry (actually there's alot more in the query but I'm hoping this 'cut down' version will make it clearer). One Role has many TimeTableEntries and their related via the ro_role_serial and tn_role_serial. The TimeTableEntry table also has a tn_sequence field. I want to get fields from the Role record and from the TimeTableEntry record that relates to it with the lowest tn_sequence no. So something like:

Code:

---

select * from Role
inner join TimeTableEntry
  on (tn_role_serial = ro_role_serial
  and tn_sequence = min (tn_sequence where tn_role_sequence = ro_role_sequence)

---

Maybe this:

Code:

---

Select * From Role ro
Inner Join TimeTableEntry te on te.tn_role_serial=ro.ro_role_serial
Where te.tn_role_sequence=(Select Min(tn_role_sequence) From TimeTableEntry te2
                                Where te2.tn_role_serial=ro.ro_role_serial)

---

This is MS SQL server - right?

Yep that's done it [kicks self]

It's primarily SQL Server but it has to run over Oracle 8 as well (so I'm rephrasing it without the join syntax... but that's easy :) )

Happy Crimbo