Link to home
Start Free TrialLog in
Avatar of cjroberts77
cjroberts77

asked on

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?

Avatar of andrewbleakley
andrewbleakley
Flag of Australia image

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
Avatar of Anthony Perkins
If you can convert the list of DocID's to Xml there is an easy solution.
Avatar of cjroberts77
cjroberts77

ASKER

can you expand the case query?
>>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.
how would you use xml?
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.
I haven't used xml but am looking at anything that will solve this problem
I also, should have added that this best done using Stored Procedures.  Can you use those?
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
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>'
Be careful with Xml it is case sensitive so the attribute name "docID" is not the same as "DocID"
I get an error regarding the iDoc integer declaration "incorrect syntax near iDoc"
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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>'
Thanks for your help!