Sort Order - Alpha/Numeric

Results 1 to 4 of 4

Thread: Sort Order - Alpha/Numeric

  1. #1
    Nadi Guest

    Default 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. #2
    Eddie Campbell Guest

    Default 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. #3
    Join Date
    Dec 1969

    Default 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. #4
    Eddie Campbell Guest

    Default 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