CODEWITHSUNDEEP
sqlsqlite
Android Killer
Android Killer

Reputation: 18499

ordering with a number at end of the string

I have a table which has names like "employee 1","employee 2","employee 3" and so on and lastly "employee 10,"employee 11","employee 12".

When I am accessing all names it is coming like employee 1,employee 10,employee 11,employee 12,employee 2,employee 3 and so on.

Even if I am using "order by id" or "order by name", employee 10 is coming first then employee 1,employee 2 and son on and lastly employee 11,employee 12.

Any help will be appreciated.

Upvotes: 1

Views: 2272

Answers (4)

Stewart Macdonald
Stewart Macdonald

Reputation: 2142

If the string in front of the number is always the same length (e.g., it's always 'employee'), you could use something like the following:

SELECT * FROM employees ORDER BY ABS(SUBSTR(employeeName, 9)) ASC

Upvotes: 1

mr_wiggles
mr_wiggles

Reputation: 11

   SELECT g.groupid,
          g.groupname,
          SUBSTRING(g.groupname, 1, PATINDEX('%[0-9]%', g.groupname + '0') - 1) grouptext, 
CASE WHEN ISNUMERIC(SUBSTRING(g.groupname, PATINDEX('%[0-9]%', g.groupname + '0'), 100) + '.0e0') IS NOT NULL 
     THEN SUBSTRING(g.groupname, PATINDEX('%[0-9]%', g.groupname + '0'), 100) 
     ELSE 0 END groupnum
     FROM Groups g
 ORDER BY grouptext, groupnum;

Upvotes: 1

Aleksi Yrttiaho
Aleksi Yrttiaho

Reputation: 8446

Please note that the sorting is done using alphabetical ASCIIable sorting. The sort does not recognize that there are numbers after the word 'employee' instead each digit is handled as a separate character.

The order I would expect is 

Employee 1
Employee 10 
Employee 2
...
Employee 9

because the order is defined by

  1. If character_n in string A comes before character_n in string B, then string A comes before string B or vice versa. If the characters are equal then proceed
  2. If end of string A is reached while there are more letters in string B, then string A comes before string B or vice versa. Else go to step 1.

For example:

  • Employee 1 is shorter than Employee 10 while being equal until the end of the string.
  • Employee 10 comes before Employee 2 because 1 comes before 2 in alphabetical order.

Upvotes: 2

Vincent Savard
Vincent Savard

Reputation: 35927

As Aleksi Yrttiaho already said, it is entirely normal that Employee 10 comes before Employee 2. If you want to sort strings, you have to accept there are set rules and it cannot do what you want just because you'd like it to work that way.

If you want to order your employee from the oldest to the most recent, you have to create another column. I'd suggest a date (the date when the employee joined the company). One could suggest an incremental id, but I don't think it's the best solution. One day, you might decide that you are yourself an employee and add a row in the table containing your info, and you won't be able to sort it correctly.

Upvotes: 1

Related Questions