T-SQL: Update first row of recordset

Question

I have a query (A) that can returns multiple rows in date order:

SELECT encounter_id, department_id, effective_time
FROM   adt
WHERE  event_type IN (1,3,7)
ORDER BY effective_time

I have another query (B) that returns a single row:

SELECT encounter_id, department_id, arrival_time
FROM   ed
WHERE  event_type = 50

I would like to join the query B to query A, in such a way that query B's single row will be associated with query A's first record.

I realize that I could do this with a CURSOR, but I was hoping to use T-SQL row_number() function.

Answer 1

I found my answer:

row_number() OVER (PARTITION BY encounter_id ORDER BY encounter_id, effective_time) row.

Unfortunately, the database has data-quality issues that prevent me from approaching the solution this way.

Thanks for your assistance.

Answer 2

Hi Craig,

Not sure if i got the question right. Let me know if the below solution is different than what you were expecting

SELECT *
FROM
(
    SELECT   TOP 1
      encounter_id, department_id, effective_time 
    FROM     adt 
    WHERE    event_type IN (1,3,7) 
    ORDER BY effective_time 
)adt1,
(
    SELECT   encounter_id, department_id, arrival_time 
    FROM     ed 
    WHERE    event_type = 50 
) ed1

then you can join both the tables as per your need, using WHERE clause

Regards, Niyaz