0

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?

Yuschick
  • 2,642
  • 7
  • 32
  • 45
  • A couple of ways come to mind, maybe somebody might even want to code up an example, but it's a pain. Anyways, you can use a temp table to create an order for the values that are in your IN clause and parse them into the temp table using http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85 . The other method would be to use a cursor and return row by row. The temp table is probably faster. – RandomUs1r Feb 25 '13 at 17:09

1 Answers1

5

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786