distinct result by entire table when using cross apply on json and order by with case

Question

I need to order my result by value from dictionary that stored as JSON in my table that equals a parameter. In order to get it I'm using case on my order by to check if the value from the dictionary match the parameter. After ordering the table I need to distinct the result however I'm getting an error and I couldn't figure it out.

here is my query:

declare @FilteredItemIDs -> temp table that filtered my items

declare @CurrentGroupID as int

select distinct item.*
from Items as items
    outer apply openjson(json_query(Data, '$.itemOrderPerGroup'), '$') as X
where items.ItemID in (select ItemID from @FilteredItemIDs )
order by case
    when @CurrentGroupID!= 0 and (JSON_VALUE(X.[Value], '$.Key') = @CurrentGroupID) then 1
    else 2 end,
    CONVERT(int, JSON_VALUE(X.[Value], '$.Value'))

Answer 1

When you DISTINCT over a resultset, you are effectively using GROUP BY over all columns. So the X.Value doesn't exist anymore when you get to the ORDER BY.

Using DISTINCT is usually a code smell, it indicates that joins have not been thought through. In this case, you should probably place the OPENJSON inside a subquery, with SELECT TOP (1), although it's hard to say without sample data and expected results.

select
  item.*
from Items as items
outer apply (
    select top (1)
      X.[Key],
      X.Value
    from openjson(Data, '$.itemOrderPerGroup')
      with (
          [Key] int,
          Value int
      ) as X
) X
where items.ItemID in (select ItemID from @FilteredItemIDs )
order by case
    when @CurrentGroupID != 0 and X.Key = @CurrentGroupID then 1
    else 2 end,
    X.[Value];

Note the correct use of OPENJSON with a JSON path and property names.

If what you actually want is to filter the OPENJSON results, rather than ordering by them, then you can do that in an EXISTS

select
  item.*
from Items as items
outer apply  X
where items.ItemID in (select ItemID from @FilteredItemIDs )
  and exists (select 1
    from openjson(Data, '$.itemOrderPerGroup')
      with (
          [Key] int,
          Value int
      ) as X
    where @CurrentGroupID = 0 or X.Key = @CurrentGroupID
);