2

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.

1
  • This has been asked before, although it's probably not very easy to phrase the search request to find existing discussions of the issue. Here's a link to an old Stack Overflow question about the same problem, for cross-reference: Order resultset based on WHERE IN clause data
    – Andriy M
    Commented Sep 20, 2019 at 6:49

3 Answers 3

1

There is no such direct language construct. If you want to do an "unnatural order" (based on the data type), then you can either join to some other table that maps the values and has another column to define the order for each value, or you can use CASE in your ORDER BY, such as

CREATE TABLE t(col int IDENTITY)
INSERT INTO t DEFAULT VALUES
GO 8

SELECT * 
FROM t
WHERE col IN(5, 3, 4)
ORDER BY
 CASE col 
  WHEN 5 THEN 1
  WHEN 3 THEN 2
  WHEN 4 THEN 3
 END

I'm using int above, but that doesn't change the concept. If you don't like repeating the "list of values" in the ORDER BY as well as the WHERE you can skip the WHERE and do a TOP for as many rows that you want. In any case, not neat, but since there isn't such language construct...

0

Someone else might be able to suggest a better solution, but.....

This gets you what you want by using an intermediate "searching/ordering" table, it will depend on how you get the input for the where clause (i.e. whether you can successfully parse the input into the searching table, you might want to explore splitting strings and row_number() )

use tempdb
GO

drop table if exists student
drop table if exists #searching
GO


create table student (
    stud_id  varchar(10)
    , name varchar(10)
)

create table #searching (
    ordering int
    , stud_id varchar(10)
)


insert into student
select '1', 'Andrew' union all
select '2', 'Sally' union all
select '3', 'Erik' union all
select '4', 'Micheal' union all
select '5', 'John'

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

select' MrTariqJamil - not the right order'
select * from student where stud_id in (
'5',
'3',
'4'
) ORDER BY CAST(stud_id AS INT) DESC

-- ensure table is empty
delete from #searching

-- what do we want to seach for? in the order we want returned
insert into #searching
select 1, '5' union all
select 2, '3' union all
select 3, '4' 

select 'joined to the seaching table'
select student.*
from student
inner join #searching
    on student.stud_id = #searching.stud_id
order by #searching.ordering

-- different order

-- ensure table is empty
delete from #searching

-- what do we want to seach for? in the order we want returned
insert into #searching
select 1, '2' union all
select 2, '1' union all
select 3, '5' union ALL
select 4, '4' union ALL
select 5, '3'

select 'now in a different order'
select student.*
from student
inner join #searching
    on student.stud_id = #searching.stud_id
order by #searching.ordering
-2
select * from student where stud_id in (
'5',
'3',
'4'
)

Just add at the end of the statement ORDER BY CAST(stud_id AS INT) DESC

1
  • The suggested ORDER BY (gives us 5, 4, 3) doesn't correspond to the asking order (5, 3, 4). Commented Sep 19, 2019 at 7:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.