SQL subquery: How to grap the record with both the min(date) and min(hour)

Question

I have a table from which I want to grap the first record that gives a passage IN ('B410','B420','C430','C440'). So, I suppose this means looking for the record with the oldest date and within that oldest date the record with the oldest time stamp.

I wrote a query with 2 nested subqueries, but that query does not do the trick (it gives me an empty result). What am I doing wrong? The strange thing is that when changing the syntax to find the max(fromdate) and max(hour), I do find the record with timstamp 22:18. But I do not find the record with timestamp 16:40 with min(fromdate) and min(hour).

select v.id,
    h.fromdate,
    h.hour,
    h.department,
    h.room
from visit v
inner join visit_hist h
on  v.id = h.id
where v.id in ('10251183')
    and h.room in ('B410','B420','C430','C440')
    and h.fromdate =
    (select min(fromdate)
    from visit_hist
    where (id= h.id
        and h.hour =
        (select min(hour) from visit_hist where id= h.id and h.date = date
        ))
    )
order by v.date,
    v.visit_id;

Can someone help me out here?

Answer 1

Just use window functions:

select . . .
from visit v inner join
     (select vh.*, row_number() over (partition by vh.id order by date asc, hour asc) as seqnum
      from visit_hist vh
      where vh.room in ('B410', 'B420', 'C430', 'C440')
     ) vh
     on v.id = vh.id
where v.id in (10251183) and seqnum = 1;

Note: This gives the ids with the first room in the list. If you want the ids whose first room is in the list, then move the condition on vh.room to the outer query.