T-SQL: Update first row of recordset
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 quer开发者_运维知识库y 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.
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
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.
精彩评论