CODEWITHSUNDEEP
sql-server-2008
Hussam Kazah
Hussam Kazah

Reputation: 98

In the select clause, getting multiple fields from a subquery

How to improve this query?

select ID, Name
       ,(select top 1 Item FROM Items where BeneficiaryID = a.ID 
          order by PickupDateTime desc) as Item
       ,(select top 1 FontColor FROM Items where BeneficiaryID = a.ID 
          order by PickupDateTime desc) as FontColor
       ,(select top 1 BackColor FROM Items where BeneficiaryID = a.ID 
          order by PickupDateTime desc) as BackColor
FROM Beneficiary a
where Name like N'%Sam%'

When I try the 3 fields in the same subquery I get:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I need to get the beneficiary data with the latest items that was picked up stored in the items table.

I tried doing it with a left join but did not get the desired results.

Upvotes: 0

Views: 41

Answers (2)

Eid Morsy
Eid Morsy

Reputation: 966

Try this using cte clause & row_number()

With itemsData as (
select BeneficiaryID, FontColor,BackColor, PickupDateTime
, row_number() over (partition by BeneficiaryID         order by PickupDateTime desc) rn
From items)
Select a.ID,a.Name,b.item,b.FontColor,b.Backcolor     
FROM Beneficiary a left join itemsData b on b.BeneficiaryID = a.ID and b.rn=1
where a.Name like N'%Sam%'

Query without cte clause 

Select a.ID,a.Name,b.item,b.FontColor,b.Backcolor     
FROM Beneficiary a 
left join (select BeneficiaryID, FontColor,BackColor, PickupDateTime
, row_number() over (partition by BeneficiaryID    order by PickupDateTime desc) rn
From items) b on b.BeneficiaryID = a.ID and b.rn=1
where a.Name like N'%Sam%'

Upvotes: 0

Horaciux
Horaciux

Reputation: 6477

SELECT  a.ID, a.Name, b.Item, b.FontColor, b.BackColor
FROM Beneficiary a
CROSS APPLY
        (
        SELECT  TOP 1 Items.Item, Items.FontColor, Item.BackColor
        FROM    Items
        WHERE   a.ID = Items.BeneficiaryID 
        ORDER BY PickupDateTime DESC
        ) b
WHERE a.Name like N'%Sam%'

Upvotes: 1

Related Questions