0

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

3
Contributors
3
Replies
5
Views
6 Years
Discussion Span
Last Post by jlego
0

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..

1

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

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.