Sort Order - Alpha/Numeric

# Thread: Sort Order - Alpha/Numeric

## Sort Order - Alpha/Numeric

I need to sort a list of records being displayed. The field in the table looks like this - 1,2,3,4,10,12,30,43,A,B,C. But when I call it (SELECT DISTINCT Fieldname FROM table where fieldname = strfieldname order by fieldname ASC) then I get the results as 1,10,12,2,3,30,4,43,A,B,C - instead of 1,2,3,4,10,12,30,43,A,B,C - I know it has something to do with the Apha - numeric sequence - can U help??? THANX

2. Eddie Campbell Guest

## Tricky question

OK, this is a little tricky to do, but not impossible.<BR><BR>The important thing is to know what the maximum length of the data in your field is going to be. In my example, I have assummed that it will never hold a vlaue longer than 10 chars.<BR><BR>What you need to do is create an expression which adds a number of zeros to the front of the numeric characters so that 1 becomes<BR>0000000001 and 10 becomes 0000000010. For the A, B, C values, we just leave them as they are and they will sort below the numeric values in alpha order.<BR><BR>Have a look at this code:<BR><BR> dbConn.Open<BR> Cmd = "SELECT Distinct FieldName FROM Table ORDER BY IIf(IsNumeric([FieldName]),String(10-Len([FieldName]),&#039;0&#039;) & [FieldName],[FieldName])"<BR> set Myrs = Server.CreateObject("ADODB.Recordset")<BR> Myrs.Open cmd, dbConn, 3,3<BR> Do While Not(MyRs.Eof)<BR> Response.Write MyRs("FieldName") & "<BR>"<BR> MyRs.MoveNext<BR> Loop<BR><BR><BR>This should output your data in 1,2,3,4,10,12,30,43,A,B,C order.

3. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: Sort Order - Alpha/Numeric

at a guess I&#039;d say the column is a text field? right? try using a numeric field.<BR><BR>j

4. Eddie Campbell Guest

## But...

the data the poster showd is a mixture of numeric and alpha. The only way I can think of sorting it is the way I posted above.

#### Posting Permissions

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