Select specific records in a specific order without using a general condition

Question

My goal:

I have a list of "StudentID" ... Let them say: 4, 2, 3, 5, 7 (for example, stored in an array), and I want to make a select statement that returns the StudentID and StudentName of the specified student identifier in the list, so same list order .

Thus, the result should be:

StudentID StudentName
4         Philip
2         Mary
3         Tima
5         Lara
7         Michel

How can i achieve this?

Answer 1

using the join request, you need to enter the order parameter / value in the sql file.

select studentId, studentName from (
    select 1 as rowOrder, studentID, studentName from <table> where studentID = 4 UNION ALL
    select 2, studentID, studentName from <table> where studentID = 2 UNION ALL
    select 3, studentID, studentName from <table> where studentID = 3 UNION ALL
    select 4, studentID, studentName from <table> where studentID = 5 UNION ALL
    select 5, studentID, studentName from <table> where studentID = 7) as x
order by rowOrder

Answer 2

temp . temp .

create table #temp (
    SortID int identity,
    StudentID int
)

insert into #temp 
    (StudentID)
    select 4 union all
    select 2 union all
    select 3 union all
    select 5 union all
    select 7

select s.StudentID, s.StudentName
    from StudentTable s
        inner join #temp t
             on s.StudentID = t.StudentID
    order by t.SortID

Answer 3

case , :

select 
  StudentID, 
  StudentName 
from 
  <table>
where 
  StudentID in (4,2,3,5,7)
order by 
  case studendID 
     when 4 then 1 
     when 2 then 2
     when 3 then 3
     when 5 then 4
     when 7 then 5
   end;

Answer 4

select studentID, studentName 
from Students 
where studentID in (4, 2, 3, 5, 7)

Answer 5

, /, .

declare @IDs varchar(max)
set @IDs = '4,2,3,5,7'

;with cte
as
(
  select
    left(@IDs, charindex(',', @IDs)-1) as ID,
    right(@IDs, len(@IDs)-charindex(',', @IDs)) as IDs,
    1 as Sort
  union all
  select
    left(IDs, charindex(',', @IDs)-1) as ID,
    right(IDs, len(IDs)-charindex(',', IDs)) as IDs,
    Sort + 1 as Sort
  from cte
  where charindex(',', IDs) > 1
  union all
  select
    IDs as ID,
    '' as IDs,
    Sort + 1 as Sort
  from cte
  where
    charindex(',', IDs) = 0 and
    len(IDs) > 0
)
select
  cte.ID as StudentID,
  Students.StudentName
from cte
  inner join Students
    on cte.ID = Students.StudentID  
order by cte.Sort

, . SO .

Answer 6

:

Select * From Employees Where Employees.ID in(1,5,2,3) 
ORDER BY CHARINDEX(','+CONVERT(varchar, Employees.ID)+',', ',1,5,2,3,')