Order By Problem

Hi,

I have a query
SELECT * FROM kb_document where DocID='206' or DocID='295' or DocID='120' or DocID='302' or DocID='303' or DocID='327' or DocID='338' or DocID='345'

I want the result to be ordered by the positions of the DocID values in the query.ie. I want the result to start with 206 first.  I get the result order by the wrong value
120
206
295
302
303
327
338
345

Any suggestions?

cjroberts77Asked:
Who is Participating?
 
Anthony PerkinsCommented:
I don't know. It worked fine for me.  Are you sure you copied and pasted the whole stored procedure?  Here is what I would do:

1. Copy the following (everything between the lines):
-----------------------------------------------------------------------------------------------------------------------
Create Procedure usp_GetDocuments
                         @RequestXml varchar(8000)             -- Change to text data type if more than 8000

As

Declare     @iDoc integer

SET NOCOUNT ON

EXEC sp_xml_preparedocument @iDoc OUTPUT, @RequestXml

Select     k.*
From     kb_document k
     Inner Join OpenXml(@idoc, 'Request/ID', 1) With (
          Rank integer '@rank',
          DocID integer '@docID') x On k.DocID = x.DocID

Order By Rank
EXEC sp_xml_removedocument @idoc
-----------------------------------------------------------------------------------------------------------------------

2. Paste it in SQL Query Analyzer.  

3. Execute it.  You should get "The command(s) completed successfully." (I did)
0
 
andrewbleakleyCommented:
I can only see this way to do it

SELECT * FROM kb_document
Rank=
CASE DocId
WHEN '206' THEN 1
WHEN '295' THEN 2

....
END
where DocID='206' or DocID='295' or DocID='120' or DocID='302' or DocID='303' or DocID='327' or DocID='338' or DocID='345'
ORDER BY Rank ASC
0
 
Anthony PerkinsCommented:
If you can convert the list of DocID's to Xml there is an easy solution.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cjroberts77Author Commented:
can you expand the case query?
0
 
Anthony PerkinsCommented:
>>can you expand the case query?<<
If you mean on the fly, than the answer is no, at least not without using Dynamic SQL.  That is why Xml is a better solution.
0
 
cjroberts77Author Commented:
how would you use xml?
0
 
Anthony PerkinsCommented:
The question you have not answered is:
Can you use Xml?

In other words are you prepared to pass an Xml document that looks something like the following:
<Request>
      <ID order="1" value="206"></ID>
      <ID order="2" value="295"></ID>
      <ID order="3" value="120"></ID>
      <ID order="4" value="302"></ID>
      <ID order="5" value="303"></ID>
      <ID order="6" value="327"></ID>
      <ID order="7" value="338"></ID>
      <ID order="8" value="345"></ID>
</Request>

As you can see this not only gives the value but also the order.
0
 
cjroberts77Author Commented:
I haven't used xml but am looking at anything that will solve this problem
0
 
Anthony PerkinsCommented:
I also, should have added that this best done using Stored Procedures.  Can you use those?
0
 
cjroberts77Author Commented:
Yeah for sure. I could pass a list of docid's in the order i need them and then use the sp to return the order i need
0
 
Anthony PerkinsCommented:
Your Stored procedure would look like this:
Create Procedure usp_GetDocuments
                         @RequestXml varchar(8000)             -- Change to text data type if more than 8000

As

Declare      @iDoc integer

SET NOCOUNT ON

EXEC sp_xml_preparedocument @iDoc OUTPUT, @RequestXml

Select      k.*
From      kb_document k
      Inner Join OpenXml(@idoc, 'Request/ID', 1) With (
            Rank integer '@rank',
            DocID integer '@docID') x On k.DocID = x.DocID

Order By Rank
EXEC sp_xml_removedocument @idoc


You could then test it as follows in SQL Query Analyzer:

exec dbo.usp_GetDocuments
      '<Request>
           <ID rank="1" docID="206"></ID>
           <ID rank="2" docID="295"></ID>
           <ID rank="3" docID="120"></ID>
           <ID rank="4" docID="302"></ID>
           <ID rank="5" docID="303"></ID>
           <ID rank="6" docID="327"></ID>
           <ID rank="7" docID="338"></ID>
           <ID rank="8" docID="345"></ID>
      </Request>'
0
 
Anthony PerkinsCommented:
Be careful with Xml it is case sensitive so the attribute name "docID" is not the same as "DocID"
0
 
cjroberts77Author Commented:
I get an error regarding the iDoc integer declaration "incorrect syntax near iDoc"
0
 
Anthony PerkinsCommented:
If it did, than test it as follows in SQL Query Analyzer:

exec dbo.usp_GetDocuments
     '<Request>
          <ID rank="1" docID="206"></ID>
          <ID rank="2" docID="295"></ID>
          <ID rank="3" docID="120"></ID>
          <ID rank="4" docID="302"></ID>
          <ID rank="5" docID="303"></ID>
          <ID rank="6" docID="327"></ID>
          <ID rank="7" docID="338"></ID>
          <ID rank="8" docID="345"></ID>
     </Request>'
0
 
cjroberts77Author Commented:
Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.