SQL Sorting Question 1

[dvannoy]

Trying to figure this out.

DOC#
PAGE
LINE

SELECT DOC,PAGE,LINE FROM TABLE
ORDER BY PAGE,LINE ASC;

Shows the following;

DOC PAGE LINE
12345 1 1
12345 1 2
12345 2 10
12345 2 5
12345 2 6

How can I sort so line 5 and 6 on page 2 come before line 10 on page 2?

any help would be appreciated

Thanks

 

[tcsbiz]

The output leads me to believe that Line is defined as varchar (or some other string). Line needs to be integer (or some other numeric) for it to sort the way you want.

Tom

 

[dvannoy]

Just checked the field... your right.

Thanks

 

[gmmastros]

What you are seeing is an alpha sort. What you want is a numeric sort.

The best way to solve this problem is to change the data type of the LINE column to integer (or other suitable number type).

If you cannot change the data type of the LINE column, then this becomes a bit more complicated. In fact, the best way to achieve the results you want is to sort it twice. If the data is numeric, then convert to number and sort. Next level sort would be by the data itself.

For example:

Declare @Temp Table(Line VarChar(20))

Insert Into @Temp Values('1')
Insert Into @Temp Values('2')
Insert Into @Temp Values('10')
Insert Into @Temp Values('Eight')
Insert Into @Temp Values('Six')
Insert Into @Temp Values('')
Insert Into @Temp Values('0')

Select * From @Temp Order By Line

Select * 
From   @Temp 
Order By 
       Case When IsNumeric(Line + '.0e0') = 1 Then Convert(Int, Line) Else NULL End,
       Line

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom