Getting back set order from the IN param

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

Re: Getting back set order from the IN param

Yobbo wrote:
> Hi All
>
> My query is as follows:

WHAT DATABASE!?!@?!?!

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

By "SQL", do you mean MS SQL Server? What version???

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

Sort of.

You have to admit: that's not a natural sort order, is it? How could a query
engine know to put 234 before 19? Believe me, it is not looking at your
input parameters. it is generating a resultset and then applying a sort
order to that resultset based on the contents of that resultset. BTW,
without an "order by" clause, you have no right to be complaining about the
order in which you receive the results.

However, there are two approaches you can use to get your desired sort
order:
a) a temp table into which you insert your input values along with a ranking
value
b) a union query

a) This would most easily be accomplished using a stored procedure to which
you passed a delimited string containing the input values.Parse the string
and insert the input values into a temp table along with a ranking value.
See Erland Sommarskog's article about handling arrays and lists in T-SQL for
help with parsing the string - http://www.sommarskog.se/arrays-in-sql.html.
The result should be something like:

insert into #tmp (input, rank)
values (6,1)
insert into #tmp (input, rank)
values (20,2)
etc.

Then use a join between the temp table and webstrings along with an "order
by" clause to retrieve your results in the desired order:
select STRINGTEXT, TOKENID
FROM WEBSTRINGS w JOIN #tmp t
ON w.TOKENID = t.input
ORDER BY t.rank


b) I don't recommend this because it requires the use of dynamic sql, which
can leave you vulnerable to hackers using sql injection, however, for the
sake of completeness, the idea is to dynamically create a union query to
retrieve your results:

select STRINGTEXT, TOKENID
FROM
(select 1 rank,STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID = 6
UNION ALL
select 2 rank,STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID = 20
etc
) q
ORDER BY rank

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Getting back set order from the IN param

Hi Bob

Many thanks for the valuable info as always.

I have to admit, my db is MySQL, but I thought nobody would answer my query
if I put this. I class the issue as a generic SQL problem, but obviously
the solutions provided are possibly going to be SQL Server specific.

I just wish there was a rich vein of experts in a MySQL NG like there is in
this one.

As for the 'order by', I did have ORDER BY TOKENID, but this would
definitely generate my problem. I just thought that if I leave it off my
example I would eliminate anybody saying 'well your order by is sorting it
numerically for a start!'.

Once again many thanks.

Rgds

Yobbo



"Bob Barrows [MVP]" wrote in message
news:O5aBInNAHHA.3928@TK2MSFTNGP03.phx.gbl...
Yobbo wrote:
> Hi All
>
> My query is as follows:

WHAT DATABASE!?!@?!?!

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

By "SQL", do you mean MS SQL Server? What version???

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

Sort of.

You have to admit: that's not a natural sort order, is it? How could a query
engine know to put 234 before 19? Believe me, it is not looking at your
input parameters. it is generating a resultset and then applying a sort
order to that resultset based on the contents of that resultset. BTW,
without an "order by" clause, you have no right to be complaining about the
order in which you receive the results.

However, there are two approaches you can use to get your desired sort
order:
a) a temp table into which you insert your input values along with a ranking
value
b) a union query

a) This would most easily be accomplished using a stored procedure to which
you passed a delimited string containing the input values.Parse the string
and insert the input values into a temp table along with a ranking value.
See Erland Sommarskog's article about handling arrays and lists in T-SQL for
help with parsing the string - http://www.sommarskog.se/arrays-in-sql.html.
The result should be something like:

insert into #tmp (input, rank)
values (6,1)
insert into #tmp (input, rank)
values (20,2)
etc.

Then use a join between the temp table and webstrings along with an "order
by" clause to retrieve your results in the desired order:
select STRINGTEXT, TOKENID
FROM WEBSTRINGS w JOIN #tmp t
ON w.TOKENID = t.input
ORDER BY t.rank


b) I don't recommend this because it requires the use of dynamic sql, which
can leave you vulnerable to hackers using sql injection, however, for the
sake of completeness, the idea is to dynamically create a union query to
retrieve your results:

select STRINGTEXT, TOKENID
FROM
(select 1 rank,STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID = 6
UNION ALL
select 2 rank,STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID = 20
etc
) q
ORDER BY rank

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"