SQL select query order by on where in

Question

How Can I make the order by based on what I input on where?

example query

select * from student where stud_id in (
'5',
'3',
'4'
)

the result would be

id|   name  |
5 |  John   |
3 |  Erik   |
4 | Michael |

Kindly help me thanks.

Answer 1

As Gordon mentioned, you need something to provide order. An IN clause doesn't have a pre-defined order, just like a table doesn't. Rather than numbering the row order yourself, you could have a table variable do it like this:

DECLARE TABLE @StudentIDs
(
    StudentIDKey int IDENTITY(1,1) PRIMARY KEY,
    StudentID int
);

INSERT @StudentIDs (StudentID)
VALUES 
(5),
(3),
(4);

SELECT *
FROM Student AS s
INNER JOIN @StudentIDs AS id
ON s.StudentID = id.StudentID
ORDER BY id.StudentIDKey;

That should be far easier if you have a lot of values to work with.

Hope that helps.

Answer 2

One method is with a derived table:

select s.*
from student s cross join
     (values (5, 1), (3, 2), (4, 3)
     ) v(stud_id, ord)
     on v.stud_id = s.stud_in
order by v.ord;

stud_id looks like a number so I dropped the single quotes. Numbers should be compared to numbers. If it is really a string, then use the single quotes.