ORDER BY, only orders by the first character?

Hi,

I am using a stored procedure that is databound to a dropdownlist. The stored procedure works fine, aside from the fact that the ORDER BY only sorts the list of items using the first character in the cell. E.g. The numbers 1, 20, 100 and 200 would be ordered as follows:

1

100

20

200

clearly i am doing something wrong and i apologise if it is a stupid question, but could anyone please offer me some help?

Thank you,

Shep

ORDER BY a numeric data type as opposed to ORDER BY a character data type.

Run the following code in a test database. Note the absence of indexes.

CREATE TABLE dbo.OrderByInt(number int)

GO

INSERT INTO dbo.OrderByInt(number) VALUES(1)

INSERT INTO dbo.OrderByInt(number) VALUES(10)

INSERT INTO dbo.OrderByInt(number) VALUES(100)

INSERT INTO dbo.OrderByInt(number) VALUES(2)

INSERT INTO dbo.OrderByInt(number) VALUES(20)

INSERT INTO dbo.OrderByInt(number) VALUES(200)

SELECT number FROM dbo.OrderByInt

SELECT number FROM dbo.OrderByInt ORDER BY number

CREATE TABLE dbo.OrderByVarchar(number varchar(3))

GO

INSERT INTO dbo.OrderByVarchar(number) VALUES('1')

INSERT INTO dbo.OrderByVarchar(number) VALUES('10')

INSERT INTO dbo.OrderByVarchar(number) VALUES('100')

INSERT INTO dbo.OrderByVarchar(number) VALUES('2')

INSERT INTO dbo.OrderByVarchar(number) VALUES('20')

INSERT INTO dbo.OrderByVarchar(number) VALUES('200')

SELECT number FROM dbo.OrderByVarchar

SELECT number FROM dbo.OrderByVarchar ORDER BY number

SELECT number FROM dbo.OrderByVarchar ORDER BY CAST(number AS int)

|||

It sounds like you're storing numbers as text (in other words, storing "int" values in a "varchar" column). So, SQL Server is doing a text sort, which does indeed go by the first character.

The best solution is to use a data type that matches the data (use "int" for integer numbers, and nvarchar for text).

An alternative solution is to use a CAST or CONVERT in your order by to convert it to "int" before sorting -- ORDER BY CAST(myColumn AS int) -- but any sort of bulk operation on an expression like that cripples SQL Server's query optimizer and will hurt performance on large tables.

-Ryan / Kardax

Edit: "lkh" beat me by seconds, and provides a nice illustration of the problem and solution.

|||

Thank you for your reply lkd, however i am still having trouble:

When I try the above code i get the error message 'TABLE NOT SUPPORTED BY THIS EDITOR'

I am using Visual Web Developer 2005 Express Edition, could this be the problem?

Shep

|||

Sorry I haven't fully explained myself (i missed the most important part, my apologise!)

i am working in units of voltage & power, so there for the listing would be as follows:

1kW

10W

100W

20W

200W

so i dont think its an option to convert the values into integers, given that I have other non-numeric characters in there! Is it a lost cause?

Shep

|||I have never used Visual Web Developer but in a quick tour of the web site I don't see anything about creating objects in a database. You may have to use an editor in your database.|||

My honest though probably not helpful thought is that this is a database design problem. Can you redesign the table to have a column called Watt of int datatype?

Otherwise you could create a complicated CASE statement and sort on that column but that is not ideal.

|||Well the table is supplying the items for the dropdownlist, based on a certain product type. So just using numbers without the corresponding measurements would not look right in the dropdownlist.|||Can you add an int column that wouldn't appear in the UI to the table that would be used in the SELECT statement that populates the dropdown list?|||

If your are only using the single rightmost character ( as in your examples) as alpha, then the following may work for you.

BOTH strip off the last character, AND convert to an Integer ONLY in the ORDER BY clause.

DECLARE @.MyTestTable table
( MyColumn varchar(5) )

INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '2A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '10W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '1A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '100W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '200W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '20W' )

SELECT MyColumn
FROM @.MyTestTable
ORDER BY cast( left( MyColumn, ( len( MyColumn) - 1 )) AS int )

|||

Hi,

thanks to everyone for their help, in the end i have resorted to simply using VB to code in the items depending on the category: e.g.

If Value = 2 Then

ddlPara1.Items.Add(New ListItem("50V", "50V"))

ddlPara1.Items.Add(New ListItem("120V", "120V"))

ddlPara1.Items.Add(New ListItem("150V", "150V"))

ddlPara1.Items.Add(New ListItem("300V", "300V"))

ddlPara1.Items.Add(New ListItem("400V", "400V"))

If Value = 3 Then........

This shouldnt affect the performance of my application should it?

Thanks again,

Shep