CODEWITHSUNDEEP
sqlasp-classicsql-order-by
DoomageAplentty
DoomageAplentty

Reputation: 2732

SQL Order By CharIndex()

I am running a pretty lengthy SQL query that at its core is pretty basic:

SELECT * FROM documents WHERE doc_id IN (995,941,940,954,953,973)

My goal result of this query is to then display the documents in the order in which they are set in the IN clause. However, I have yet to find a clean solution for doing so.

I thought I could use charindex() to handle this.

ORDER BY charindex('995,941,940,954,953,973',doc_id)

The result of that ORDER BY just orders the doc_ids in default ASC order.

Any guidance on how I could specifically define the result order this query?

Upvotes: 0

Views: 3060

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Your charindex is backwards:

order by charindex(doc_id, '995,941,940,954,953,973')

If the doc_id is stored as a number, then you need to do a cast:

order by chardindex(cast(doc_id as varchar(255)),  '995,941,940,954,953,973')

And, you might consider putting the doc ids in a list. Then they can be used throughout the query:

with doclist as (
      select 1 as ordering, '995' as docid union all
      select 2 , '941' union all
      . . .
)
select . . .
from . . .
     left outer join
     doclist dl
     on docid = dl.docid
order by dl.ordering

Upvotes: 5

Related Questions