In the select clause, getting multiple fields from a subquery

Question

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.

Possibly related: https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row — stephen.vakil, Sep 08 '17 at 18:30

score 1 · Answer 1 · answered Sep 09 '17 at 03:32

1

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%'

answered Sep 09 '17 at 03:32

Horaciux

6,322
2
22
41

Thanks for your answer but it seems I needed an OUTER APPLY. SELECT a.ID, a.Name, b.Item, b.FontColor, b.BackColor FROM Beneficiary a OUTER APPLY ( SELECT TOP 1 c.Item, c.FontColor, c.BackColor FROM Items c WHERE a.ID = c.BeneficiaryID ORDER BY PickupDateTime DESC ) b WHERE Name like N'%Sam%' – Hussam Kazah Sep 09 '17 at 12:39
The performance of the query in my question and the above query is the same. – Hussam Kazah Sep 09 '17 at 12:47
@HussamKazah About performance. First, you will always scan the hole table if use `LIKE '%x'` no index is used. Second, I will add a datetime column in Beneficiary table and keep it updated (could be a trigger) with lastPickupDatatime (or it could be an ID on Items) . The query will be a regular inner join and with an index on this column in items it would be the fastest solution – Horaciux Sep 09 '17 at 14:51

Eid Morsy · Answer 2 · 2017-09-09T17:03:37.120

0

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%'

edited Sep 09 '17 at 17:03

answered Sep 09 '17 at 01:34

Eid Morsy

966
6
8

Thanks for your answer Eid. I needed it as one query. It is not part or a stored procedure. – Hussam Kazah Sep 09 '17 at 12:41
I didn’t understand what do you mean by “one query” but I’ve edited the answer by adding new query using only join without CTE , hope it helps. – Eid Morsy Sep 09 '17 at 17:06

In the select clause, getting multiple fields from a subquery

2 Answers2