February 20, 2019 at 9:28 am
Hello SQL guru's
i have an order by statement like so:
ORDER BY
Suppliers.Descr,
ItemGroups.Descr,
Items.Descr,
Sum(TOTALINV.NGUQOH),
ITEMS.UNITCOST,
Items.SupplierPartNumber,
which returns the expected rows
if i change the ITEMS.UNITCOST to COALESCE (ITEMS.UNITCOST, 0)
the Row order is not the same
I do have a ROW_NUMBER() OVER(ORDER BY(SELECT SUPPLIERS.DESCR)) AS ROWNUMBER, in the initial SELECT statement
but am not ordering by this field
Is this known behaviour? If is do a CONVERT, MONEY on the same field it also affects the ordering in the same way
Thanks! All comments and replies appreciated 🙂
Andy
February 20, 2019 at 9:46 am
Couple of things that come to my mind without any knowledge of data in the table - can unit cost be < 0 and can unit cost be null - because that will affect the ordering
Thanks
February 20, 2019 at 9:48 am
andrew.ingpen 41964 - Wednesday, February 20, 2019 9:28 AMHello SQL guru'si have an order by statement like so:
ORDER BY
Suppliers.Descr,
ItemGroups.Descr,
Items.Descr,
Sum(TOTALINV.NGUQOH),
ITEMS.UNITCOST,
Items.SupplierPartNumber,which returns the expected rows
if i change the ITEMS.UNITCOST to COALESCE (ITEMS.UNITCOST, 0)
the Row order is not the same
I do have a ROW_NUMBER() OVER(ORDER BY(SELECT SUPPLIERS.DESCR)) AS ROWNUMBER, in the initial SELECT statement
but am not ordering by this fieldIs this known behaviour? If is do a CONVERT, MONEY on the same field it also affects the ordering in the same way
Thanks! All comments and replies appreciated 🙂
Andy
Hi Andy,
your question is not very clear . But 1 quick question does ITEMS.UNITCOST column contains any negative value?
Saravanan
February 20, 2019 at 10:01 am
Thanks for the quick replies, i thought my question was pretty clear - the COALESCE and CONVERT in the ORDER BY are affecting the ordering.
I possibly obscured my question by including the fact that i am using ROW_NUMBER OVER in the select as i thought that may be having an influence on the output / execution plan
I think you are bothe correct with the obvious answer that NULL's in the output will affect the order - i will see if i can reproduce the problem in a public DB like northwind etc and post a full
example
cheers
Andy
February 20, 2019 at 10:26 am
andrew.ingpen 41964 - Wednesday, February 20, 2019 10:01 AMThanks for the quick replies, i thought my question was pretty clear - the COALESCE and CONVERT in the ORDER BY are affecting the ordering.
I possibly obscured my question by including the fact that i am using ROW_NUMBER OVER in the select as i thought that may be having an influence on the output / execution plan
I think you are bothe correct with the obvious answer that NULL's in the output will affect the order - i will see if i can reproduce the problem in a public DB like northwind etc and post a full
example
cheers
Andy
Did you understand why we asked that question? NULL has the lowest precedence and it is always sorted first unless any specific conditions is given.
Saravanan
February 20, 2019 at 1:23 pm
I guess I look at this differently. Why would you ever expect such constructs NOT to affect the order? NULL values would clearly cause the ordering to change, and whether you used a COALESCE or an ISNULL function might not matter. When you used 0 as the substitute value for the ORDER BY in your COALESCE, that value is the one used for sorting for any row with a NULL value, so that might at least explain why all the rows with NULLs would appear where a 0 value would appear. Not sure why you would expect to get some other kind of result, though... Where were you expecting rows with NULL values to appear in the sort order? Last? If that's the case, you should have used 999999999 as your substitute value, or alternatively, whatever the largest possible value is for the data type of that column, thus forcing the NULL values to sort last.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 21, 2019 at 12:57 pm
My first question was whether ITEMS.UnitCost was character. If so, the coalesce would change the sort method from alphabetic to numeric, which could have a profound effect on the sort order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 25, 2019 at 12:07 pm
>> I have an order by statement like so: <<
ORDER BY is a clause and not a statement. So you're starting off on a completely wrong. This is a fundamental concept and you're missing it. But things get worse after that. SQL is based on logic, and the first law of logic is called the Law of Identity; this states "to be is to be something in particular, to be nothing in particular or anything in general is to be nothing at all." If you're an old Ayn Rand fan, you remember the phrase "a is a" is an expression of this foundation of all Western thought. That means there is no such thing as your magic universal "<generic>_decsr" instead of a particular kind of description.
ORDER BY
Suppliers.descr,
Item_Groups.descr,
Items.descr,
SUM(Total_Inv.ngugoh),
Items.unit_cost,
Items.supplier_part_nbr
This is followed by a function call, an aggregate function call in fact! The whole idea the order by clause is it appears as part of a cursor (either implicit or explicit cursors) . Cursors only work on sequential file structures. You can't do aggregate in the middle of a sequential file!
Since we had no DDL because you didn't bother to read the forum netiquette or feel exempted from it, we cannot look at your DDL or your DML. Without any more information than we have, I would have guessed that the item groups would be an attribute of an item and not a separate entity in your data model. If they change frequently, then it might be worth factoring them out.
Would you like to share the complete problem with us?
>> I have an order by statement like so: <<
ORDER BY is a clause and not a statement. So you're starting off on a completely wrong. This is a fundamental concept and you're missing it. But things get worse after that. SQL is based on logic, and the first law of logic is called the Law of Identity; this states "to be is to be something in particular, to be nothing in particular or anything in general is to be nothing at all." If you're an old Ayn Rand fan, you remember the phrase "a is a" is an expression of this foundation of all Western thought. That means there is no such thing as your magic universal "<generic>_decsr" instead of a particular kind of description.
ORDER BY
Suppliers.descr,
Item_Groups.descr,
Items.descr,
SUM(Total_Inv.ngugoh),
Items.unit_cost,
Items.supplier_part_nbr
This is followed by a function call, an aggregate function call in fact! The whole idea the order by clause is it appears as part of a cursor (either implicit or explicit cursors) . Cursors only work on sequential file structures. You can't do an aggregate in the middle of a sequential file!
Since we had no DDL because you didn't bother to read the forum netiquette or feel exempted from it, we cannot look at your DDL or your DML. Without any more information than we have, I would have guessed that the item groups would be an attribute of an item and not a separate entity in your data model. If they change frequently, then it might be worth factoring them out.
Would you like to share the complete problem with us?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply