Skip to content
Advertisement

Is there a way to use IN and it evaluate values left to right in T-SQL?

Simplified example:

select top(1) team.points
from teams
where team.ID = '123' and team.season IN ('2021', '2015', '2010') 

I have been searching and cannot find a solid answer on this. I want to use the IN clause to get certain rows, but I want it to work left to right.

In the example above, I would want a row for 2021, then 2015, then 2010 (assuming no seasons were null).

Is there a way to make the IN clause care about order?

EDIT: So by the comments, I can tell my example was no good. I have edited it to hopefully show what the issue was. I am looking for one result, but I need to check the values in the IN clause IN ORDER, and the actual data in my field was not numerical or easily ordered. In the comments it was suggested to use case statements in the order by clause to make this field where it can be ordered. I was unaware you could use case statements in the order by clause. This seems to have resolved my issue.

Advertisement

Answer

The following code demonstrates a couple of ways of filtering and ordering the data. Any can be modified to return just top (1) Points, but the full output is shown for clarity.

-- Sample data.
declare @Teams as Table ( Id VarChar(4), Season VarChar(4), Points VarChar(4) );

insert into @Teams ( Id, Season, Points ) values
  ( '1', '2010', 'VC' ), ( '12', '2010', 'CX' ), ( '123', '2010', 'XI' ),
  ( '1', '2015', 'VCI' ), ( '12', '2015', 'CXI' ), ( '123', '2015', 'XII' ),
  ( '1', '2021', 'VCII' ), ( '12', '2021', 'CXII' ), ( '123', '2021', 'XIII' );

select * from @Teams;

-- OP's original query without any explicit order.
select Id, Season, Points
  from @Teams
  where Id = '123' and Season in ( '2021', '2015', '2010' );

-- With order imposed by a   case   expression.
--   For each   Season   the   case   expression needs to be extended to supply an  appropriate value.
select Id, Season, Points
  from @Teams
  where Id = '123' and Season in ( '2021', '2015', '2010' )
  order by case Season
    when '2021' then 1
    when '2015' then 2
    when '2010' then 3
    else 42 end; -- Put unexpected values last.

-- With order and filtering by a   values   clause (equivalent to a separate table).
--   For each   Season   there needs to be an additional row in   Seasons   to specify both the
--     season and its order.  This has the benefit of keeping the pairs of values together.
select T.Id, T.Season, T.Points
  from @Teams as T inner join
    ( values ( '2021', 1 ), ( '2015', 2 ), ( '2010', 3 ) )
    as Seasons( Season, SeasonOrder ) on T.Season = Seasons.Season
  where T.Id = '123'
  order by Seasons.SeasonOrder;
4 People found this is helpful
Advertisement