i need help in SQL?

Question

i have a table named tblPerson. There is a field in it named PersonID. the data type of the PersonID is nvarchar(5).
there were 9999 rows in the table when i added the series of 10000-10010 it is not sorting the data as it should it. it is showing 10001 - 10010 after the rows of 1000 which is wrong why is it what should i do?

Answer 1

Why did you place an integer value in a string field.
I assume that you started with PersonID of 1000.

You should go through each table that references or relates to PersonID and change that field into an integer type.

This involves for each table that contains a reference to PersonID, create a tempPersonID field of type int.
write a program to assign all of the values of the PersonID field to the tempPersonID field.

Drop all relationships to the PersonID field.
Drop all indexes that contain the PersonID field.
Drop the PersonID field in each table
Add the PersonID field back into each table it was dropped from this time as an int.

write a program to Assign the value of the tempPersonID field to the PersonID field
Drop the tempPersonID field.
Add the indexes that were dropped.
Add the relationships that were dropped.

Check all of your store procedures that referenced these fields.

or you can add a sortPersonID field that is an integer and is maintained programmaticall in your programs. Use this field to sort by.

Or Better Yet.

Why don't you sort by Lastname, Firstname, Middlename.

Or can you sort by a field such as date-added?

Answer 2

Vasudhaika is correct. Because the field type is set to nvarchar(5) it is treating the numbers as it would letters of the alphabet. I recommend changing the fieldtype for PersonID to int and then the following query should work fine:

SELECT PersonID FROM tblPerson
ORDER BY PersonID

Answer 3

Try the following SQL

Select Convert(Numeric, PersonID) From tblPerson

Regards
Muhammad Salman

Answer 4

You said that the datatype is NVARCHAR
then when it tries to sort the field it will sort the data with the first letter as 1 like 1,10,100,1000,11,110...2,20,20...

if you are trying to sort the data in query try access the data in the query by converting the data as double in the query it self like

Select * from Tblperson order by cdbl(personid)

it will work.

Answer 5

You said that the datatype is NVARCHAR
then when it tries to sort the field it will sort the data with the first letter as 1 like 1,10,100,1000,11,110...2,20,200....

if you are trying to sort the data in query try access the data in the query by converting the data as double in the query it self like

Select * from Tblperson order by cdbl(personid)

it will work.

Answer 6

Unless you specifically used "order by" it is not defined to display any kind of sorting. nvarchar is not a numerical type so the indexing is by character ASCII code.