Query ordering

1. Senior Member
Join Date
Dec 1969
Posts
506

## Query ordering

I need to order a query by the order of items in an "IN" clause and I can&#039;t seem to get it figured out (may be the drugs I&#039;m on for my kidney stones :-)). For example, in this SQL statement:<BR><BR>SELECT Id, FirstName, LastName<BR>FROM FooTable<BR>WHERE Id IN(6, 2, 9)<BR><BR>I want the records returned in as such:<BR><BR>6, "Bob", "Doe"<BR>2, "Mary", "HadALilLamb"<BR>9, "Alex", "White"<BR><BR>Instead, the rows a returned in order of ID by default. Any ideas?

2. Senior Member
Join Date
Dec 1969
Posts
2,334

## you can't expect an order

by statement to generate rows in any order.<BR><BR>Don&#039;t think you can do that.

3. Senior Member
Join Date
Dec 1969
Posts
973

## might be a solution by using arrays

You can use arrays to sort your recordset.<BR>1. From (6,2,9)<BR>sortArray = split("6,2,9" ",")<BR>sortArry(0) = 6<BR>sortArry(1) = 2<BR>sortArry(2) = 9<BR>2. Place you recordset into an array<BR>3. Display the records according to sortArry, retrieving the ID value in your recordset array.<BR><BR>THis might work depending on the number of records your are return. Might be alittle dirty but it might work for your needs.

4. Senior Member
Join Date
Dec 1969
Posts
506

## This was my initial solution but...

Ugh :-) I was hoping not to have to loop through two arrays! Ah well. It&#039;s a situation where we won&#039;t have more than 20 rows. Still ugly though!

5. Senior Member
Join Date
Dec 1969
Posts
973

## yup

yup, but if you need it to be sorted..... might be a better solution though.... I will give it alittle more thought

6. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: Query ordering

SELECT Id, FirstName, LastName<BR>FROM FooTable<BR>WHERE Id IN(6, 2, 9)<BR>order by case when 6 then 1<BR> when 2 then 2<BR> when 9 then 3 end<BR><BR>Even though it is standard sql it is not supported by all dbms.

7. Senior Member
Join Date
Dec 1969
Posts
2,437

## Typo in that

SELECT Id, FirstName, LastName <BR>FROM FooTable <BR>WHERE Id IN(6, 2, 9) <BR>order by case id when 6 then 1 <BR> when 2 then 2 <BR> when 9 then 3 end <BR>

8. Senior Member
Join Date
Dec 1969
Posts
2,334

## One small correction

FROM FooTable<BR>WHERE Id IN(6, 2, 9)<BR>order by case ID <BR>when 6 then 1<BR>when 2 then 2<BR>when 9 then 3 end<BR><BR>Boy, I couldn&#039;t think of that.<BR><BR>But that&#039;s good for small number of rows, right?<BR>

9. Senior Member
Join Date
Dec 1969
Posts
506

## ****, I like it!

Looks good. I&#039;m going to use it! I never even thought of the case statement. Sweet.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•