SQL 2000, IN clause, and soft order...

Question

SQL 2000, IN clause, and soft order...

DearMichael

Grasshopper

Points: 20
More actions
September 6, 2006 at 3:32 pm

#119445

I have a stored procedure that accepts a comma-delimited list of IDs, then searches a table returning the associated records...

The simplified statement:
select * from aTable WHERE anID IN (7,8,9,1,10,6) <--passed variable
Currently, the query results are returned as sorted: 1,6,7,8,9,10
but I need the results to stay in the order they are presented in the IN clause: 7,8,9,1,10,6
Is there no command to tell the SQL Server NOT to sort the results, but return the rows as requested...?

How do I create an ORDER BY from a variable list of IDs?

Thank you,
Michael
Michael@MasonHolt.com

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 1

If you are not specifying order by clause in your select statement data will not sorted in any order, it will fetch as it was inserted into the table

There is no such statement in SQL, which orders the result set by values in column

If you still needs it

Split your input using fn_split function (you can find this in this forum)

write as many selects as input count (use dynamic sql) , use union to combine them in that order

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

Try this:

select * from stores WHERE Stor_ID IN (7067,6380,8042,7896)

order by CharIndex(stor_id,'7067,6380,8042,7896')

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 3

sorry, not working

CharIndex will give you the starting position of the specified expression in a character string

In order by clause if you specify an integer value (returned by CharIndex funtion), the output will be ordered by the specified column position in select statement , If there is no such position in select , then it will result to default order

I think this will help

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

I'm just gonna take a guess and say that you didn't actually try it Gopi... works fine from what I can see...

stor_id stor_name stor_address city state zip

------- --------------------- --------------------- ------------ ----- -----

7067 News & Brews 577 First St. Los Gatos CA 96745

6380 Eric the Read Books 788 Catamaugus Ave. Seattle WA 98056

8042 Bookbeat 679 Carson St. Portland OR 89076

7896 Fricative Bookshop 89 Madison St. Fremont CA 90019

(4 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

Michael,

That means that, using Sreejith's fine example, your code should look something like this...

select * from aTable WHERE anID IN (7,8,9,1,10,6) <--passed variable

order by CHARINDEX(anID,@passedvariable)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 6

Jeff,

Just now I tried again with same query, I got it ..here in example anID should be of character type, if it is of integer type query is not compiling at all

Thanks

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 7

Yep... I believe you are correctm Gopi... didn't realize the Stor_ID in the Stores table of PUBS was actually a CHAR(4).

Soooooo..... the correct code should look something more like this if anID is an INT...

select * from aTable WHERE anID IN (7,8,9,1,10,6) <--passed variable

order by CHARINDEX(CAST(anID AS VARCHAR(10)),@passedvariable)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DearMichael Grasshopper Points: 20 More actions · Answer 8

THANK YOU! THANK YOU!

I have racked my brain trying to find a solution.

Very much appreciated!

Michael

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 9

Our pleasure... thanks for the feedback...

Sreejith... nice job... you hit the nail on the head... I just played "cleaning lady" on this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)