How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence ?In order perform a sort you need to use the ORDER BY clause. If the datatype
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions