CODEWITHSUNDEEP
sqlsql-servert-sql
Bob
Bob

Reputation: 21

Left join with sub select

I need to assign an address to the patient. The one with the highest date is always current in the address list. I would select this with "max". I compiled the query according to the examples I found here, but if I put more than one column in the sub-select in the select, it doesn't work. Where is the mistake? I need to join tables using "p.PatientId = x.SubjectId", but only always with one row from table x. MS-SQL

    SELECT * FROM Patient p
    LEFT JOIN (
                select SubjectId, max(LastUpdated) from  AddressBase
                where AddressTypeId = '1'
                group by SubjectId
              ) x  ON p.PatientId = x.SubjectId

// Output: No column name was specified for column 2 of 'x'.

This works, but returns more results than it should:

SELECT * FROM Patient p
LEFT JOIN (
            select SubjectId from  AddressBase
            where AddressTypeId = '1'
            group by SubjectId
          ) x  ON p.PatientId = x.SubjectId

Upvotes: 0

Views: 400

Answers (3)

Denis Khizhniak
Denis Khizhniak

Reputation: 26

To fix the error message in your first query you just need to add an alias to your second column, which is max(LastUpdated), for example like this:

max(LastUpdated) as LastUpdated

Though this will not give you the expected results as here you will just select the maximum value of the column "LastUpdated" for each SubjectId / PatientId.

What you need is to use a window function that will numerate LastUpdated values and then you will be able to select the maximum one for each of the patients, and the Address field as well:

SELECT *
FROM Patient p
LEFT JOIN  (
   SELECT SubjectId,
          Address
   FROM (
      SELECT SubjectId,
             Address,
             Row_number() OVER (PARTITION BY SubjectId
                                ORDER BY LastUpdated DESC) AS rn
      FROM AddressBase
      WHERE AddressTypeId = '1'
      ) q
   WHERE q.rn = 1
   ) x ON p.PatientId = x.SubjectId

I don't have a chance to validate the query but hope it will give you the right idea.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

Instead of an outer join use OUTER APPLY with a TOP clause.

select * 
from patient p
outer apply
(
  select top(1) *
  from addressbase ab
  where ab.subjectid = p.patientid
  and ab.addresstypeid = 1
  order by ab.lastupdated desc
);

Upvotes: 2

Popeye
Popeye

Reputation: 35900

You can use analytical function as follows:

Select * from
(SELECT p.*, a.required_columns,
        Row_number() over (partition by p.PatientId order by LastUpdated desc) as rn
   FROM Patient p
   LEFT JOIN AddressBase a on AddressTypeId = '1'
         And p.PatientId = x.SubjectId) t
 Where rn = 1

Upvotes: 0

Related Questions