Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Order By Problem

Posted on 2005-05-16
15
Medium Priority
?
183 Views
Last Modified: 2012-05-05
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?

0
Comment
Question by:cjroberts77
  • 8
  • 6
15 Comments
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 14015795
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14021429
If you can convert the list of DocID's to Xml there is an easy solution.
0
 

Author Comment

by:cjroberts77
ID: 14082706
can you expand the case query?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14082715
>>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
 

Author Comment

by:cjroberts77
ID: 14082724
how would you use xml?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14082741
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
 

Author Comment

by:cjroberts77
ID: 14082805
I haven't used xml but am looking at anything that will solve this problem
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14082825
I also, should have added that this best done using Stored Procedures.  Can you use those?
0
 

Author Comment

by:cjroberts77
ID: 14082849
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14082906
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14082919
Be careful with Xml it is case sensitive so the attribute name "docID" is not the same as "DocID"
0
 

Author Comment

by:cjroberts77
ID: 14082953
I get an error regarding the iDoc integer declaration "incorrect syntax near iDoc"
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 14083022
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14083027
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
 

Author Comment

by:cjroberts77
ID: 14083075
Thanks for your help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question