April 29, 2012 at 8:54 am
Hi All
I have a table with two columns
which is Name:ExamAnswers
QuestionNumber int
Answer varchar(15)
it has a total of 36 rows questionnumber starting at 1 and finishing at 36
Im trying to order it by Question number so i was hoping it would return like this
1
2
3
4
5
etc
all the way down to question 36 but when i say order by QuesitonNumber acs i get
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
28
29
3
30
31
32
33
34
35
36
4
5
6
7
8
9
Order by Desc is just as bad
how can i order it so its starts at 1 and finishes at 36?
Select QuestionNumber, UserAnswer
From ExamAnswers
where UserID = 1
order by QuestionNumber asc
April 29, 2012 at 9:17 am
If it's ordering 1, 10, 11, 12, etc, than that column is not an integer, it's a string. That's string ordering.
Convert the column to integer (which, if it's numeric it should be anyway) and it will order correctly.
To demonstrate:
CREATE TABLE StringOrdering (
ID VARCHAR(10)
);
CREATE TABLE IntegerOrdering (
ID INT
);
INSERT INTO StringOrdering (ID)
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY object_id)
FROM sys.columns AS c
INSERT INTO IntegerOrdering (ID)
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY object_id)
FROM sys.columns AS c
SELECT * FROM StringOrdering
ORDER BY ID;
SELECT * FROM IntegerOrdering
ORDER BY ID;
See how the integer column sorts 'correctly', while the string column sorts the way you describe your query to be doing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2012 at 9:23 am
Yeah you were right i had it as a string! damn it!
Thanks for the information and snippet!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply