How to append metadata to a grouped selection by primary key

Question

I have a list of favorites like:

Sample Data

| key | item_id | list_name |  customer_id |meta           |
|-----|---------|-----------|--------------|---------------|
| 1   | A-11    | aa11      | 001          | unique-data-1 |
| 2   | A-11    | bb22      | 001          | unique-data-2 |
| 3   | A-26    | cc33      | 001          | unique-data-3 |
| 4   | A-28    | aa11      | 002          | unique-data-4 |
| 5   | J-52    | aa11      | 001          | unique-data-5 |
| 6   | X-53    | aa11      | 001          | unique-data-6 |

Desired Output

for @item_id nvarchar(20) = 'A-11'

| key | isFavorited | list_name | meta          |
|-----|-------------|-----------|---------------|
| 1   | Y           | aa11      | unique-data-1 | 
| 2   | Y           | bb22      | unique-data-2 |
| 3   | N           | cc33      | unique-data-3 |

And would like to return a selection of all available lists, as well as whether or not a particular item is part of that list, with its meta data.

declare @item_id nvarchar(20) = 'A-11'
declare @customer_id nvarchar(20) = 001

select
[key],
[isFavorited] = max(case when [item_id] = @item_id then 'Y' else 'N' end)
[list_name]
[meta]
from favorites
where customer_id = @customer_id
group by [list_name], [key], [meta]

Issues when trying various methods:

1. The issue I'm having is that since the meta is unique the group by destroys the uniqueness of the select

2. A cross apply like the following doesn't apply the correct meta based on a matching key.

   cross apply (
     select top 1 
     [meta]
     from favorites
      where customer_id = @customer_id
   )
   

3. When selecting by row number, the actual key to join back to is lost, so I'm unable to join the meta.

   "noRow" = row_number() over(order by h_po_no asc)
   

I'd like to

1. Pass in an item_id and customer_id
2. Return all lists for that customer
3. Get favorite status for each list of passed in item_id
   • An item is flagged favorite if it matches both list_name and item_id for a given customer_id
4. Get row primary key and meta data

How can I return a distinct selection of list_name, isFavorite status, key, and it's meta?

Answer 1

Test Parameters

declare @item_id nvarchar(20) = 'A-11'
declare @customer_id nvarchar(20) = 001

Solution

drop table if exists #tmp

;with cte as (
  select
  [key],
  [list_name],
  [rn] = row_number() over (partition by list_name order by list_name desc)
  from favorites
  where customer_id = @customer_id
  group by [list_name], [key], [meta]
)
select *
into #tmp
from cte
where [rn] = 1

select 
[i],
[json],
#t.[tmp]
from #tmp #t
inner join (
  select
  [isFavorited] = max(case when [item_id] = @item_id then 'Y' else 'N' end)
  [list_name]
  from favorites
  where customer_id = @customer_id
  group by [list_name]
) j 
on j.list_name = #t.list_name