ORDER BY - Need to understand what's going on

Question

ORDER BY - Need to understand what's going on

sgmunson

SSC Guru

Points: 110640
More actions
May 22, 2019 at 6:54 pm

#3646274

Okay, folks... this one has me puzzled. I have a query that sorts by the Warehouse, the location, and the part, but if I run a separate query that supplies the same values, I don't see the same order, even though the order by operates on the same values and in the same order in the ORDER BY clause. Take a look see at this screen-shot:
Any ideas how this is even possible? I would have said collation, but the two queries are both operating on the default collation for the database, so why are these values NOT sorting in the same order. It's my understanding that a shorter string with the same characters as a longer string, just at the beginning of that longer string, should sort before the longer string. That's not happening in the first query, and I'd like to know why. Any chance it's a bug that I'm just not aware of? The level of SQL Server for 2008 could easily be something well less than the latest. Might not be able to do anything about it, but sure would love to know what is going on... Anyone?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Can you post your query?

Looking at the screenshots the first result set shows row 91 and the second result set shows row 1. Since no row in data set 2 appears in the screenshot for data set 1, something is different in your queries (which is expected as you have no column names in the second query).

Without seeing the queries, we have no way of guessing what is different... could be a WHERE clause, could be the order of arguments to ORDER BY... heck, from our side it could be 2 completely different data sets. We cannot see your query or your data apart from that screenshot which doesn't tell us anything.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Are you using column aliases in the order by?

Sue

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I have to agree, need to see the code, DDL, and some sample data that recreates the issue.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

I have no way to provide sample data that will recreate the problem. In fact, that 2nd query result was nothing but the following:

SELECT 'ASSBLY', RTRIM('SN5B3 ASSBLY'), '820035021', 'A' UNION ALL

SELECT 'ASSBLY', RTRIM('SN5B3'), '820035021', 'Z'

ORDER BY 1, 2, 3

The first query, while not entirely disclosable, has the following ORDER BY clause and I'm showing the first 3 columns selected:

SELECT

ISNULL(W.[NAME], W.ID) AS WAREHOUSE,

RTRIM(IT.LOCATION_ID) AS [LOCATION],

P.ID AS PART_ID,

.

ORDER BY

ISNULL(W.[NAME], W.ID),

RTRIM(IT.LOCATION_ID),

P.ID;

There is a GROUP BY and a HAVING within the undisclosed query, and the GROUP BY is identical to the ORDER BY clause. I can't reproduce the problem outside of the original query.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You are comparing apples to oranges. The ORDER BYs aren't the same. If you can't recreate the issue it tells me that it has something to do with the actual data, not the query itself.

drew.allen SSC Guru Points: 76996 More actions · Answer 6

My first thought is unprintable characters, e.g., tabs. I usually cast to binary to see if the binary data matches what I'm expecting.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sgmunson SSC Guru Points: 110640 More actions · Answer 7

That's what I first thought, and then I copied the data into the second query, and also just ran some test SELECT statements and thought for sure I had covered those possibilities. Turns out when I specifically search for the TAB character, bingo, that appears. However, that still violates what I thought the rule would be: that two string values where one value is just more characters added to the end of the other value, would result in the longer string sorting second, even if the additional characters would have tabs or other un-printables. Still don't like this result. But, since I now know the cause, I can go around it. Thanks everyone.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)