1

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.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Android Killer
  • 18,174
  • 13
  • 67
  • 90
  • what is your query you have tried post here – Roshan Wijesena Aug 11 '11 at 05:30
  • 2
    This should be great read for your: [Sorting for Humans : Natural Sort Order](http://www.codinghorror.com/blog/2007/12/sorting-for-humans-natural-sort-order.html) – Jacob Aug 11 '11 at 05:32
  • "table with names" or rows with values? – p.campbell Aug 11 '11 at 05:33
  • This question could be retitled as 'How to achieve natural sort order in SQLite?' It might then better fit the questions http://stackoverflow.com/questions/153633/natural-sort-in-mysql, http://stackoverflow.com/questions/3158917/natural-sort-for-sql-server, http://stackoverflow.com/questions/34518/natural-sorting-algorithm etc. – Aleksi Yrttiaho Aug 11 '11 at 05:46
  • By the way, is the id equal to the number after the name? If yes, that should be enough to order the result by _if_ the id column is numeric and not a character. If the id is characters, either change the type or if you are sure that there are only numbers, convert the char to a number before using it for sorting. – Aleksi Yrttiaho Aug 11 '11 at 05:51
  • @campbell of course rows with values – Android Killer Aug 11 '11 at 05:54

4 Answers4

2

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.
Aleksi Yrttiaho
  • 8,266
  • 29
  • 36
  • I completely understand it but i am searching for any way to solve this problem.Any idea? – Android Killer Aug 11 '11 at 05:40
  • Either you have to write your own sorting algorithm or separate the number to a different column. Is there a reason why you would repeat the word employee in the table? – Aleksi Yrttiaho Aug 11 '11 at 05:41
  • Yes it is going to be displayed in screen. – Android Killer Aug 11 '11 at 05:56
  • Could you concatenate the word instead of storing it then e.g. `SELECT 'Employee ' || id as employee FROM employees ORDER BY CAST(id AS INTEGER) ASC`. Better yet, have the view layer of your program to add the word 'Employee' which would allow you to support internationalization as well. – Aleksi Yrttiaho Aug 11 '11 at 06:04
  • If i am not wrong it will take id as integer but id contains character we cannot take it as integer. – Android Killer Aug 11 '11 at 06:12
  • It's hard to guess more solutions :) If you can, create a new column with the employee numbers as integers, concatenate the employee number with a constant 'Employee' and order by the new column in ascending order and everything should be fine. – Aleksi Yrttiaho Aug 11 '11 at 06:17
  • ok thanks.But database is very big.So it will be problem.Little weak in query.:d – Android Killer Aug 11 '11 at 06:30
  • With that change the database will get smaller by 9n bytes where n is the number of employees so it's a win-win! – Aleksi Yrttiaho Aug 11 '11 at 06:53
1
   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;
mr_wiggles
  • 11
  • 1
1

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
Stewart Macdonald
  • 2,062
  • 24
  • 27
1

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.

Vincent Savard
  • 34,979
  • 10
  • 68
  • 73