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.
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;