Numbering sequence order not proper when use string in SQL Query

Gani tpt
Gani tpt used Ask the Experts™

I want to print sequentially in a column. my query displaying select,1,10,11,12,etc.

But, i want to display select,1,2,3,4,5,6,7,etc

how to do this...?

below is my SQL query.

Select ' Select ' Emplevel,'' EmpId Union
select EmpLevel, ISNULL(EmpId,'') EmpId
from tbl_Employee order by Emplevel
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
>when use string in SQL Query
This is your problem, as when ordering a character column (such as varchar, char)  it will order by character order (1, 10, 11, 12, 2, 21) and not numeric order (1, 2, 10, 11, 12, 21).    

So if you wish a column to sort numerically then it needs to be a numeric data type.

Or at minimum use CAST(EmpID as int) in your query so that the query converts the column to an int, which will then sort numerically.
Gani tptTech Lead cum developer



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial