Sorting Using >> IN(234,23,354)

# Thread: Sorting Using >> IN(234,23,354)

1. Senior Member
Join Date
Dec 1969
Posts
661

## Sorting Using >> IN(234,23,354)

Hi,<BR><BR>This is really an SQL question. How can you sort your record set when using IN just by the order you put it in.<BR><BR>IN(234,23,354)<BR><BR>I want it to sort<BR>234<BR>23<BR>354<BR><BR>It seems to always be random. Anyone know an ORDER BY way to do this?<BR><BR>Ideas?<BR><BR>Paul<BR>

2. Senior Member
Join Date
Dec 1969
Posts
228

## Unless I'm mistaken...

...you can&#039;t<BR><BR>[Anyone correct me if I&#039;m wrong here]<BR><BR>The way the IN clause works is that for each record in the table, SQL will check if the current ID is in the list.<BR><BR>For as far I know, you can&#039;t sort them this way.<BR><BR>I don&#039;t know what you are trying to do but you MIGHT wanna try the .sort method of the recordset<BR><BR>myRecordset.sort = "ID ASC"<BR><BR>OR<BR><BR>when you build your IN clause in ASP, put the values in an array, sort the array then put the sorted numbers in the IN clause.<BR><BR>for instance...<BR><BR>myArray = Array(354, 234, 23)<BR>&#039;.sort does not exist on arrays, I&#039;m no big "sorter" so I&#039;ll let you figure how to do it :)<BR>myArray.Sort()<BR><BR>For i=LBound(myArray) to UBound(myArray)<BR> strWhereClause = strWhereClause & myArray(i) & ","<BR>Next &#039;i<BR>strWhereClause = "WHERE ID IN (" & Left(strWhereClause, Len(strWhereClause)-1) & ")"<BR><BR>But again....I&#039;m not even sure this would work<BR><BR>Stephane

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

## RE: Sorting Using >> IN(234,23,354)

where c in (234,23,354)<BR>order by case c<BR>when 234 then 1<BR>when 23 then 2<BR>when 354 then 3 end

4. Senior Member
Join Date
Dec 1969
Posts
96,118

For Access:<BR><BR>WHERE c IN (234,23,354)<BR>ORDER BY InStr( &#039;,234,23,354,&#039;, &#039;,&#039; & CStr(c) & &#039;,&#039; )<BR><BR>Since the query is presumably built from VBScript, this is easy:<BR><BR>&#060;%<BR>SQL = "SELECT ... FROM sometable " _<BR> & " WHERE c IN (" & theList & ") " _<BR> & " ORDER BY InStr( &#039;," & theList & ",&#039; , &#039;,&#039;&CStr(c)&&#039;,&#039; )"<BR>...<BR>%&#062;<BR><BR>I guess you could do something similar for SQL Server, too, but the CASE...WHEN strikes me as cleaner there.<BR><BR>

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

## Sql server solution

WHERE c IN (234,23,354)<BR>ORDER BY charindex( &#039;,&#039; + Cast(c as varchar) + &#039;,&#039;, &#039;,234,23,354,&#039; )<BR>

6. Senior Member
Join Date
Dec 1969
Posts
661

## RE: Sql server solution << Great Idea

Thanks Lars,<BR><BR>This works great. I need to get a better SQL book. The SAMs "in 10 minutes" does not cut it for more complicated scripts. Any recommendations?<BR><BR>Paul

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

## RE: Sql server solution << Great Idea

Well, I haven&#039;t read any SQL books myself but J Celko has written some that discusses some more complicated problems. <BR><BR>If you are specially interested in SQL server:<BR><BR>The Guru&#039;s guide to Transact SQL by Kim Henderson <BR><BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## If you found that in *ANY* book...

...I&#039;d be surprised.<BR><BR>It&#039;s just a case of knowing how various functions work and then how to coerce your data to fit their workings.<BR><BR>For example, the reason I stuck the commas on the front and back of the converted-to-string field value was to make sure you didn&#039;t get false matches.<BR><BR>If we hadn&#039;t used them, and had done (Access version of the code, easy to translate now to TSQL):<BR><BR> ORDER BY InStr( &#039;234,23,387&#039;, CStr(c) )<BR><BR>and if field c had a value of 23 (which at least on of the records presumably would), you&#039;d get a false match of 23 with 234!<BR><BR>By prefixing and suffixing both the list and the values with commas, now you are searching in<BR> ,234,23,387,<BR>for the value<BR> ,23,<BR>and no false match on ,234, of course.<BR><BR>So that&#039;s really *nothing* to do with SQL, per se. You&#039;d use that same technique in any programming language if you needed to solve that same problem. It&#039;s just that you need to be able to see an overall solution when given pieces (functions, in TSQL) of the solution.<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
228

## RE: Sorting Using >> IN(234,23,354)

That&#039;s it....I found my SQL master<BR><BR>*bow*<BR><BR>LOL, that was strong man.<BR><BR>Stephane Dorion

#### Posting Permissions

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