select specific records with specific ordering without using a common condition

My purpose the following:

I have a list of "StudentID"... Let's say: 4, 2, 3, 5, 7 (stored in an array for example), and I want to make a select statement that returns StudentID and StudentName 开发者_如何学JAVAof the specified studentID in the list, with the same order of the list.

So the result should be:

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

How can I achieve this?

I would get that array of IDs into a temp table and join against that temp table for the select. Creating an identity column in the temp table will preserve the desired order.

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

If your database supports case in select statements, the following returns the queried data in the desired order:

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;

with a union query, you need to inject an order parameter/value to the sql tough.

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

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

Here is a version that splits your comma separated string/array and then used in a join to Students.

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

BTW, there's more than one way to split a string. A search on SO will give you a lot to pick from.

Try this:

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