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?
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?
If you can convert the list of DocID's to Xml there is an easy solution.
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.
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.
ASKER
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.
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.
ASKER
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?
ASKER
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>'
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"
ASKER
I get an error regarding the iDoc integer declaration "incorrect syntax near iDoc"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>'
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>'
ASKER
Thanks for your help!
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