CODEWITHSUNDEEP
c#sql-serversql-order-by
Cody Dull
Cody Dull

Reputation: 135

ORDER BY giving wrong order

I have an SQL statement in my C# program that looks like:

SELECT * FROM XXX.dbo.XXX 
WHERE Source = 'OH'
  AND partnum = '1231202085' 
ORDER BY partnum, Packaging, Quantity

When running this query in SQL Server Management, the results are ordered as expected. My first 3 results have the same partnum and Packaging with Quantities of 32.0, 50.8, and 51.0.

However, when I run the query from my program, the result set with quantity 50.8 is the first to be returned. The datatype of Quantity is decimal(18,9). I've tried cast, it doesn't appear to be a datatype problem.

I cant figure out why its getting the middle quantity.

Thank you guys for the quick responses however after a little more testing I found my issue in my C# code, not the sql.

After getting the query results I hade:

if (PurchOrder.Read())
while (PurchOrder.Read())

Overlooking the fact that the first read would in fact read my first result, then the while whould get my second result.

Ive replace the if statement with:

if (PurchOrder.HasRows == true)

and everything looks to fine.

Again, thank you for the responses though. Sorry for the mislead question.

-Cody

Upvotes: 3

Views: 436

Answers (3)

Cody Dull
Cody Dull

Reputation: 135

Thank you guys for the quick responses however after a little more testing I found my issue in my C# code, not the sql.

After getting the query results I hade:

if (PurchOrder.Read()) while (PurchOrder.Read())

Overlooking the fact that the first read would in fact read my first result, then the while whould get my second result.

Ive replace the if statement with:

if (PurchOrder.HasRows == true)

and everything looks to fine.

Again, thank you for the responses though. Sorry for the mislead question.

-Cody

Upvotes: 0

Lynn Crumbling
Lynn Crumbling

Reputation: 13367

If partnum is alphanumeric, it's not going to sort in numerical order unless you either

  • Left Pad partnum out to the exact same number of characters

  • Sort with some specialized alphanumeric sorting. I typically use:

    order by
RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([field_name] AS VARCHAR(8000)))), 1000)

Of course, you can use much lower padding numbers, if your fields are smaller.

Upvotes: 1

JonH
JonH

Reputation: 33143

From the SQL Server side as long as partnum, packaging, and quantity are a numeric/float field this should work absolutely fine. What worries me is if packaging or partnum is a text type variable where 10 comes before 2.

To avoid this you must CONVERT or CAST the data type to an integer / decimal type before performing the ORDER BY. Look up CAST / Convert in BOL.

For instance this:

CREATE TABLE #test
(
 mytest varchar(10)
)

INSERT INTO #test(mytest) VALUES('10')
INSERT INTO #test(mytest) VALUES('1')
INSERT INTO #test(mytest) VALUES('2')
INSERT INTO #test(mytest) VALUES('12')
INSERT INTO #test(mytest) VALUES('20')

SELECT * FROM #test ORDER BY mytest
DROP TABLE #test

Produces incorrect ordering. And could be handled like so:

SELECT * FROM #test ORDER BY CAST(mytest AS INT)

As far as from the client side (C#) are you storing this in some sort of gridview?

Upvotes: 0

Related Questions