sql server - how set value depending on a column value select statement t-sql. Like if 0 set 3, if less than 4 set 2 and so on -

i have column named photos, photos column of type tinyint , vary 0 8. want apply rank column when selecting data table. values want are: if record has 4 or more photos, receives rank of 1. because fair amount of photos 5, 6, 7 , 8. if record has between 1 , 3 set rank of 2, medium rank, , if has no photos set 3. , can sort records based on number of photos , make appliation consistent.

sure, use case statement in order by. this:

select * table order    case     when photos >= 4 1    when photos >= 1 2    else 3     end 

or if wanted actual column on table, add computed column:

alter table mytable add rank  case     when photos >= 4 1    when photos >= 1 2    else 3  end