SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query IN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

misterraj
Yak Posting Veteran

India
73 Posts

Posted - 04/04/2014 :  11:42:43  Show Profile  Send misterraj a Yahoo! Message  Reply with Quote
select QID,Q1 from Qtable where QID in (600,538,534,610,612)

the answer that i get is in ascending order. i.e, i get
534
538
600
610
612

but i want to get in the order as stated above in the query.
ie., 600,538,534,610,612 ..what should i do?

Ifor
Aged Yak Warrior

576 Posts

Posted - 04/04/2014 :  12:17:51  Show Profile  Reply with Quote
In theory, a table is an UNORDERED set so the ORDER BY clause is used to order results.
As there seems to be no logic for the order of the QIDs, it needs to be specified in the query.
eg:

WITH QIDOrder(QID, QOrder)
AS
(
	SELECT 600, 1
	UNION ALL SELECT 538, 2
	UNION ALL SELECT 534, 3
	UNION ALL SELECT 610, 4
	UNION ALL SELECT 612, 5
)
SELECT Q.QID, Q.Q1
FROM Qtable Q
	JOIN QIDOrder O
		ON Q.QID = O.QID
ORDER BY O.QOrder;

or:

SELECT QID, Q1
FROM Qtable
WHERE QID IN (600, 538, 534, 610, 612)
ORDER BY
	CASE QID
		WHEN 600 THEN 1
		WHEN 538 THEN 2
		WHEN 534 THEN 3
		WHEN 610 THEN 4
		WHEN 612 THEN 5
	END;

etc
Go to Top of Page

misterraj
Yak Posting Veteran

India
73 Posts

Posted - 04/04/2014 :  12:44:26  Show Profile  Send misterraj a Yahoo! Message  Reply with Quote
Is there any other way to do this, because QID in (600,538,534,610,612), here it is only 5 elements, in actually i may have more than 100 elements.
so the query becomes huge in that case.

pls advise
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 04/05/2014 :  00:55:59  Show Profile  Reply with Quote
Got it....This may answers your Question .......


DECLARE @startingposition INT
DECLARE @InputString VARCHAR(50) ='235,23,3,6,50,4,213'
DECLARE @parts nvarchar(4000)
DECLARE @Result TABLE (ID nvarchar(4000))
SELECT @startingposition = 1
WHILE @startingposition !=0
BEGIN
SELECT @startingposition = CHARINDEX(',',@InputString)
--SELECT @Index
IF @startingposition !=0
SELECT @parts = LEFT(@InputString,@startingposition - 1)
ELSE
SELECT @parts = @InputString
--SELECT @Parts
INSERT INTO @Result(ID) VALUES(@parts)
--SELECT @Result
SELECT @InputString = RIGHT(@InputString,LEN(@InputString) - @startingposition)
--SELECT @InputString
END
SELECT * FROM @Result


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000