problem in sql query order by

[devloper]

in vb.net to store this query in dataset ..In Sql query I have a Employee Id
M 1
M 31
M 41
M 58
M 6
M 60
M 67
S 10
S 118
S 133
S 143
S 151
S 155
S 157
S 158
S 25
S 28
S_7
W 2
W 31
how it is possible by order wise like :

M 1
M 6
M 31
M 41
M 58
M 60
M 67
S_7
S 10
S 25
S 28
S 118
S 133
S 143
S 151
S 155
S 157
S 158
W 2
W 31

i m using select * from table order by employeeid asc but i don't get the proper output

[jlego]

thats because it is going to order by the alpha characters instead of the numbers..

to my knowledge, you would have to remove the letters to be able to order by the numerics..

[Teme64]

jlego has it right. Your SQL query returns the correct output which is based on ordering strings.

You didn't mention what DB you're using. Here's my quick test with SQL Server 2008:

table:
ID varchar(50)

original data:
M 6
M 31
S 28
S 118

SQL query:
SELECT * FROM [BlogTest].[dbo].[TestOrderBy] ORDER BY (Right(ID, LEN(ID)-2))

output:
S 118
S 28
M 31
M 6

if this is how you want to order employee ids.

Check your DB's documentation about built-in functions (Right() and Len()).

HTH

[jlego]

theme - i never knew that you could use the right() and len(). thanks for sharing this information - may come in handy someday + 1