|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
April 22nd, 2004, 10:15 AM
|
|||
|
|||
|
SQL order results at selection from an array
I have the sql interogation:
Code:
select col1, col2 from table where col2 in (45,67,23,45,89,90,12,47) how to obtain in results same order as in array: col1 col2 ----------------- 45 67 23 45 89 90 12 47 ---------------
|
|
#2
April 23rd, 2004, 05:39 AM
|
|||
|
|||
|
if you could dump that array into a temp table w/an identity column...
Code:
CREATE TABLE #tempTable
(
ID int IDENTITY(1,1),
Col1 int
)
INSERT into #tempTable (ID, Col1)
(select Col1 from array)
I'm not sure on the above syntax, hopefully someone can clarify. Plus, I have no idea how you actually select data from the array though. But then you could possibly have... tempTable ---------- ID Col1 1 45 2 67 3 23 4 45 5 89 6 90 7 12 8 47 Then join on it... Code:
select Col1, Col2 from table join tempTable on table.Col1 = tempTable.Col1 where Col2 in (45,67,23,45,89,90,12,47) order by tempTable.ID Last edited by Username=NULL : April 23rd, 2004 at 06:21 AM.
|
|
#3
April 23rd, 2004, 06:21 AM
|
|||
|
|||
|
Code:
select col1, col2 from table
where col2 in (45,67,23,45,89,90,12,47)
order by case
col2 when 45 then 1
when 67 then 2
when 23 then 3
-- when 45 then 4
when 89 then 5
when 90 then 6
when 12 then 7
when 47 then 8 end
|
|
#4
April 23rd, 2004, 07:14 AM
|
|||
|
|||
|
Hey man I didn't know you could order by CASE...that's pretty neat, thx for posting. Only thing I see w/that is...what if there were 100 elements in the array?
Also, if you don't mind, minus the 'array', is my syntax right in the CREATE and INSERT statements I posted?
|
|
#5
April 23rd, 2004, 07:17 AM
|
|||
|
|||
|
Well, I didn't intend that the case expression should be written by hand.
Your syntax is okay.
|
|
#6
April 23rd, 2004, 07:20 AM
|
||||
|
||||
|
your INSERT is off
you must have the same number of columns in parentheses after the table name as you have values so for your hypothetical example with ID as IDENTITY, INSERT into #tempTable (Col1) SELECT Col1 from array except you've bypassed the problem, because if you could guarantee the sequence that the values would be pulled from the "array" and inserted into the temp table, then you wouldn't need the temp table! 
|
|
#7
April 23rd, 2004, 07:56 AM
|
|||
|
|||
|
Man I'm confused...c y'all later.
Good day. Last edited by Username=NULL : April 23rd, 2004 at 08:00 AM.
|
|
#8
April 23rd, 2004, 08:00 AM
|
|||
|
|||
|
edited...
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > SQL order results at selection from an array |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
