sorting records from a sql 7 database

Results 1 to 3 of 3

Thread: sorting records from a sql 7 database

  1. #1
    Join Date
    Dec 1969

    Default sorting records from a sql 7 database

    Can someone tell me the best way to sort these records. I am using an order by but the problem is that it goes by individual character. For example I would like to display the following information sorted from lowest to highest on a ASP page. So if I had four records Name1, Name107, Name2, Name108. The order by would sort them Name1, Name107, Name108, Name2 because it just compares the one character and Name2 looks higher then Name107 and Name108 even though it is not. What I want it to do is Name1, Name2, Name107, Name108. Does anyone have any ideas? Thank you

  2. #2
    Join Date
    Dec 1969

    Default RE: sorting records from a sql 7 database

    Is Name a constant in the query or could it be anything? If it is a constant you can use<BR>select cast(substring(namefield,5,len(var1)) as int) from sometbl<BR>order by cast(substring(namefield,5,len(namefield)) as int)<BR>If Name is not a constant then maybe you can use charindex or patindex with wildcards to get the starting position of the number.<BR><BR>Sorry I can&#039;t offer more<BR>

  3. #3
    Join Date
    Dec 1969

    Default Nothing to do with SQL or SQL 7

    This is how *ALL* string sorting works.<BR><BR>You&#039;d have the same problem in every computer language I know of (Basic, C, C++, Java, JavaScript, Pascal, PL/1, COBOL, Prolog, Fortran, Algol, Lisp, Logo, and even assembly languages) and every DB I know (awww...let&#039;s not do that again).<BR><BR>Let&#039;s face it: The problem is poor data design. If you wanted to sort by a CCCCNNNN system, where the C&#039;s are non-digits and the N&#039;s are digits, you should have padded the numbers with zeroes, so instead of NAME1, NAME2, NAME107, etc., you would have NAME0001, NAME0002, NAME0107, etc.<BR><BR>Having said that... It&#039;s not too late to change the DB!<BR><BR>In the long run, that&#039;s probably the most efficient thing to do.<BR><BR>And if you can&#039;t find a way to do it with SQL code, then do it with VBScript: Iterate through every record, tear apart the field into non-digit and digit sections, and rebuild it with zero padding.<BR><BR>Having said that...the other poster is of course, right. If you have a fixed number of characters and then the digits, you can do <BR><BR>ORDER BY Left(field,4), cast(substring(namefield,5,len(namefield)) as int) <BR><BR>[I added in the LEFT... part because I didn&#039;t assume that every field content was just "Name", but do remember that this assumes that you have a *fixed* number of non-digit characters.]<BR><BR>

Posting Permissions

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