List:General Discussion« Previous MessageNext Message »
From:Benoit St-Jean Date:January 23 2004 1:02pm
Subject:Re: Order by problem
View as plain text  
Martijn Tonies wrote:

>Hi,
>
>==
>I have an ID field in my database...it reads like this
>
>ASS1
>ASS23
>ASS4
>ASS10
>ASS6
>when i'm retrieving the data by taking ORDER BY clause it is sorting like
>this
>
>ASS1
>ASS10
>ASS23
>ASS4
>ASS6
>
>means its only sorting by the 4 the character. i want the sorting to be done
>like the following
>==
>
>No, it means it's sorting by alphabet, not by the 4th character.
>
>ASS10 comes after ASS1, makes perfect sense.
>
>==
>ASS1
>ASS4
>ASS6
>ASS10
>ASS23
>
>Solutions are greatly appreciated
>==
>What you want, is that the sorting acts like to ignore "ASS" and
>use the number behind it as an integer.
>
>Perhaps you can cut off the first 3 characters, cast the rest to
>an Integer and order by that?
>
>  
>
SELECT *
FROM tablename
ORDER BY ((SUBSTRING(columnToSort FROM 4)) + 0) as numberpart

Thread
Order by problemSagar C Nannapaneni23 Jan
  • Re: Order by problemMartijn Tonies23 Jan
  • Re: Order by problemBenoit St-Jean23 Jan
  • Re: Order by problemFrederic Wenzel23 Jan
    • SELECT statementGary Broughton23 Jan
    • Re: Order by problemmos23 Jan
  • Re: Order by problemmos23 Jan