user3572565 user3572565 - 1 year ago 61
SQL Question

Selecting head records with only the first occurring line record

I have 2 tables. SHead & SLine


id - title - origin - Status - datetime


id - sid - datetime - body

I want to select all records from SHead where origin = 4. I want only the first recorded row from SLine though.

So if I have one row in SHead for origin=4 and two related rows in SLine, I only want to return one row. Is this possible?

Answer Source

Here is a typical way:

select h.*, l.*
from shead h join
     sline l
     on l.sid =
where s.original = 4 and
      l.datetime = (select min(l2.datetime) from sline l2 where l2.sid = l.sid);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download