Getting back set order from the IN param

[Yobbo]

Hi All

My query is as follows:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)

All I want is my resultset to come back in the order that I have defined in
the IN clause, but unfortunately SQL is trying to be too helpful and sorts
the numbers in the IN clause so that the resultset comes back with a TOKENID
order of 4,6,19,20,32,177,234,800.

I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

Sorry for my rant, but its got my hot under the collar.

Is there anyway round this?

Thanks

Yobbo

[Dan Guzman]

In SQL, you *must* specify ORDER BY to return data in a particular sequence.
Consider results to be unordered unless you explicitly specify ORDER BY in
the outermost query. Even if a query returned results in your desired order
without ORDER BY, it would be due to chance rather than designed behavior.

Below is one method to accomplish your task.

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
JOIN (
SELECT 6 AS TOKENID, 1 AS SEQ
UNION ALL SELECT 20, 2
UNION ALL SELECT 234, 3
UNION ALL SELECT 19, 4
UNION ALL SELECT 32, 5
UNION ALL SELECT 4, 6
UNION ALL SELECT 800, 7
UNION ALL SELECT 177, 8) AS SELECT_SEQUENCE ON
SELECT_SEQUENCE.TOKENID = WEBSTRINGS.TOKENID
ORDER BY SELECT_SEQUENCE.SEQ
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Yobbo" <in**@SpamMeNot.co.ukwrote in message
news:12*************@corp.supernews.com...

Hi All

My query is as follows:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)

All I want is my resultset to come back in the order that I have defined
in
the IN clause, but unfortunately SQL is trying to be too helpful and sorts
the numbers in the IN clause so that the resultset comes back with a
TOKENID
order of 4,6,19,20,32,177,234,800.

I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

Sorry for my rant, but its got my hot under the collar.

Is there anyway round this?

Thanks

Yobbo

[Erland Sommarskog]

Yobbo (in**@SpamMeNot.co.uk) writes:

My query is as follows:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)

All I want is my resultset to come back in the order that I have defined
in the IN clause, but unfortunately SQL is trying to be too helpful and
sorts the numbers in the IN clause so that the resultset comes back with
a TOKENID order of 4,6,19,20,32,177,234,800.

I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

Sorry for my rant, but its got my hot under the collar.

Actually, SQL Server does not sort at all. It just retrieves the rows
in the order which happens to be most efficient.

As an alterantive to Dan's suggestion, this may or may not fit better
to your actual problem:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)
ORDER BY CASE TOKENID
WHEN 6 THEN 1
WHEN 20 THEN 2
WHEN 234 THEN 3
-- etc
END

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Yobbo]

Many thanks Dan

Rgds Yobbo
"Dan Guzman" <gu******@nospam-online.sbcglobal.netwrote in message
news:co****************@newssvr27.news.prodigy.net ...
In SQL, you *must* specify ORDER BY to return data in a particular sequence.
Consider results to be unordered unless you explicitly specify ORDER BY in
the outermost query. Even if a query returned results in your desired order
without ORDER BY, it would be due to chance rather than designed behavior.

Below is one method to accomplish your task.

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
JOIN (
SELECT 6 AS TOKENID, 1 AS SEQ
UNION ALL SELECT 20, 2
UNION ALL SELECT 234, 3
UNION ALL SELECT 19, 4
UNION ALL SELECT 32, 5
UNION ALL SELECT 4, 6
UNION ALL SELECT 800, 7
UNION ALL SELECT 177, 8) AS SELECT_SEQUENCE ON
SELECT_SEQUENCE.TOKENID = WEBSTRINGS.TOKENID
ORDER BY SELECT_SEQUENCE.SEQ
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Yobbo" <in**@SpamMeNot.co.ukwrote in message
news:12*************@corp.supernews.com...

Hi All

My query is as follows:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)

All I want is my resultset to come back in the order that I have defined
in
the IN clause, but unfortunately SQL is trying to be too helpful and sorts
the numbers in the IN clause so that the resultset comes back with a
TOKENID
order of 4,6,19,20,32,177,234,800.

I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

Sorry for my rant, but its got my hot under the collar.

Is there anyway round this?

Thanks

Yobbo