September 15, 2009 at 11:17 pm
Hello Friends,
In SQL SERVER 2005, the numeric values arranged not in order, for example
the inserted values are 1 12 125 19 11 21 2 23 256
the values arranged in table is as follows?
NOT in order ----------- ORDERED NUMERICS
1 ---------------------------------- 1
11 ---------------------------------- 2
12 --------------------------------- 11
19 --------------------------------- 12
125 --------------------------------- 19
2 --------------------------------- 21
21 --------------------------------- 23
23 --------------------------------- 256
any solution for this??
September 16, 2009 at 12:23 am
If I understand your problem.. You inserted a bunch of values and when you select them they are not in order..
I can see 2 possibles.
1. Order of rows in a select statement is not assured in SQL 2005 (and above) without the use of an ORDER BY, this is largely due to parallelism.
2. On the machine you are working on there is really only one processor so parallelism isn't in play, and in that case the numeric field is not the clustered index.
The order they are physically in the table is often not relevant, but if you want to output them in a specific order you shall use an ORDER BY statement.
CEWII
September 16, 2009 at 1:01 am
The other thing that will affect things is what the datatype is.
If your field is an int or other numeric datatype and you have the output sorted on that field, you will get the data output in numeric order, eg.:
1
2
12
However, if the field is a char or some other character (alphanumeric) datatype you will see the data in character order, eg.:
1
12
2
In the second case you would need to convert the data to a numeric datatype before sorting to have it display in numeric order, eg. ... order by cast(field as int).
September 16, 2009 at 1:44 am
By default, there is no order in a set !
1 things you can do:
- Add an order by clause to your select statement.
Keep in mind order by follows the order specifics your the datatype you are handling !
(Altough this comes with a cost, you can use cast / convert in your order by clause !)
CREATE TABLE T_SSC_wrk (
IDNo INT IDENTITY(1,-10) ,
TrickyNumber AS CONVERT(varchar(15),IDNo) -- computed column, to show this example
)
go
INSERT INTO T_SSC_wrk default values
go 1000
Select *
from T_SSC_wrk
-- not ordered ! If the rows seem ordered, you just are lucky ;)
go
Select *
from T_SSC_wrk
order by TrickyNumber
go
Select *
from T_SSC_wrk
order by convert(int, TrickyNumber)
go
drop table T_SSC_wrk
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2009 at 5:39 am
i used to change the orders using ORDER BY thanks......:-)
September 17, 2009 at 5:40 am
i will change
the data type thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply