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,17 7,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
3  1717 
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.TOKE NID
ORDER BY SELECT_SEQUENCE .SEQ
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Yobbo" <in**@SpamMeNot .co.ukwrote in message
news:12******** *****@corp.supe rnews.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,17 7,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
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,17 7,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****@sommarsk og.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
Many thanks Dan
Rgds Yobbo
"Dan Guzman" <gu******@nospa m-online.sbcgloba l.netwrote in message
news:co******** ********@newssv r27.news.prodig y.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.TOKE NID
ORDER BY SELECT_SEQUENCE .SEQ
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Yobbo" <in**@SpamMeNot .co.ukwrote in message
news:12******** *****@corp.supe rnews.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,17 7,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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Mavis |
last post by:
Hi,
I'm trying to use a simple form to select by which column I will display
output from a MySQL database.
I'd like to display based on ORDER BY and select Written, Average, etc.
I know the data is getting to the script from the post, I just can't get it
to the select statement.
|
by: Jim Geissman |
last post by:
I have a function that returns a table:
CREATE FUNCTION dbo.Example(@Param int)
RETURNS @Tbl TABLE (
Field1 int,
Field2 int) AS
BEGIN
INSERT @Tbl (Field1,Field2)
SELECT FieldA,FieldB FROM DataTable
WHERE FieldC = @Param
|
by: Eyal |
last post by:
Hey,
I would appriciate if anyone can help on this one:
I have a java object/inteface having a method with a boolean
parameter. As I'm trying to call this method from a javascript it
fails on a type mismatch.
It is positively because of the boolean(java primitive)parameter. It
goes fine if I change this parameter to int or String.
This inteface has a lot more methods which works fine, it is just the
|
by: for.fun |
last post by:
Hi everybody,
I am looking for a XML comparison tool (I do not mean a standard
char-by-char diff tool but a tool which understand XML syntax)
More precisely, I can have serveral XML structures organized
differently. The XML nodes can store the same data but be organized
differently => in such a case, I would like the diff tool to tell me
that both XML files are identicals.
|
by: Peter Afonin |
last post by:
Hello,
I have a simple client-side form that is checking the domain availability on
the domain registrar's server:
<FORM action="https://www.webnames.ru/scripts/RegTimeSRS.pl" method="post">
<input type="hidden" name="thisPage" value="pispCheckDomain">
<input type="hidden" name="username" value="test">
<input type="hidden" name="password" value="test">
domain_name: <input type="text" name="domain_name"><br>
| | |
by: Yobbo |
last post by:
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
|
by: noLoveLusT |
last post by:
hi everyone i am very very new to the sql server (2 days actually and ) so
far i learned creating SPs etc but couldnt workout how to get return value
from my prodecure
my sp as follows
________________________
CREATE PROCEDURE .
@PageIndex INT,
@PageSize INT,
@Total INT OUTPUT
|
by: JohnP |
last post by:
Hi all
Does anyone know the best way to get feedback from a Windows Service?
I have written a Windows service which uses a class library (a DLL)
which has a timer which does some operations every <xseconds.
I'd like to be able to somehow get the current 'elapsed' seconds of
the timer, with a view to displaying something on the service control
manager program I have written.
All seems to be OK (service works properly, as does the DLL and...
|
by: TC |
last post by:
Hey All,
I have some classes that I recently built for a website which uses the
HttpWebRequest & HttpWebResponse objects from the System.Net namespace.
Basically, the classes rap submitted data up, connect to external websites
on external servers and post / remove the data from these other sites.
It works fine locally but when uploaded to the BCentral production server,
the outgoing requests get shutdown.
|
by: Andrus |
last post by:
How to get syntactically correct signature which compiles for code template
grneration ?
I tried code below but it creates syntactically incorrect signature.
Andrus.
using System;
using System.Collections.Generic;
public class MainClass
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
