select query IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select query IN

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-04-04 : 11:42:43

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

700 Posts

Posted - 2014-04-04 : 12:17:51

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

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-04-04 : 12:44:26

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

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-05 : 00:55:59

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.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources