| Author |
Topic |
|
melic
Starting Member
4 Posts |
 Posted - 2012-10-16 : 09:12:48
|
Hello, why the same sql returns different order results on different interogations, when there is an order by in sql? Because this order by is not enought restrictive?And what is this 'random' order that sql is applying, when the results are on same 'level' ... does anyone has any idea?Thank you in advance.  melic |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-16 : 09:37:59
|
| it doesnt apply a random order by so far as there's an explicit ORDER BY clauseI cant understand what you're telling here. Can you illustrate with some sample data what issues you're facing with ORDER BY?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-10-16 : 09:50:27
|
| Post the code you used. Are you using newid() in your ORDER BY Clause?MadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
melic
Starting Member
4 Posts |
Posted - 2012-10-16 : 09:58:58
|
| yes, of course, here is an example: for this sql SELECT Product.[IDProduct], Product.Descrizione FROM Product LEFT OUTER JOIN Marchet ON Product.IDMarca = Marchet.IdMarca INNER JOIN Category ON Product.[IDCategory] = Category.[IDCategory] ORDER BY Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriorityI have around 13000 fields result. If i run it twice in sql manager I get different order in result:after first run :AB000005 RETELE AB000006 RETELE AB000033 RETELE ...after second runAB113094 CHIAVILI AB113095 CHIAVILI AB113096 CHIAVILI ....seems that ORDER BY Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriorityis not 'strong' enough ? this are bit values, so is very probabilistic that will exist same values (of Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriority)for different products(Product.[IDProduct] )If i add also Product.[IDProduct] in order by, the result is the same every time, because this key is unique in table.What I was wondering was : sql is returning 'random' results when the order by is not enough 'restrictive' ? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-10-16 : 21:13:44
|
quote:
after first run :AB000005 RETELEAB000006 RETELEAB000033 RETELE...after second runAB113094 CHIAVILIAB113095 CHIAVILIAB113096 CHIAVILI
most probably the above have the same priority ?Your ORDER BY clause only specified the 3 priority column "Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriority" after considering the sequence / ordering based on these 3 columns, it is "random"if you want it to return a specific way, you have to be more specific in the ORDER BY clause like specifying more column in the ORDER BY clause
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-16 : 23:18:15
|
quote:
Originally posted by melic
yes, of course, here is an example: for this sql SELECT Product.[IDProduct], Product.Descrizione FROM Product LEFT OUTER JOIN Marchet ON Product.IDMarca = Marchet.IdMarca INNER JOIN Category ON Product.[IDCategory] = Category.[IDCategory] ORDER BY Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriorityI have around 13000 fields result. If i run it twice in sql manager I get different order in result:after first run :AB000005 RETELE AB000006 RETELE AB000033 RETELE ...after second runAB113094 CHIAVILI AB113095 CHIAVILI AB113096 CHIAVILI ....seems that ORDER BY Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriorityis not 'strong' enough ? this are bit values, so is very probabilistic that will exist same values (of Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriority)for different products(Product.[IDProduct] )If i add also Product.[IDProduct] in order by, the result is the same every time, because this key is unique in table.What I was wondering was : sql is returning 'random' results when the order by is not enough 'restrictive' ?
ordering by bit fields? not sure why you want to do thatyou might have to add some product related fields also------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
melic
Starting Member
4 Posts |
Posted - 2012-10-17 : 04:00:41
|
quote:
Originally posted by khtan
quote:
after first run :AB000005 RETELEAB000006 RETELEAB000033 RETELE...after second runAB113094 CHIAVILIAB113095 CHIAVILIAB113096 CHIAVILI
most probably the above have the same priority ?Your ORDER BY clause only specified the 3 priority column "Category.CategoryPriority, Marchet.MarchetPriority, Product.ProductPriority" after considering the sequence / ordering based on these 3 columns, it is "random"if you want it to return a specific way, you have to be more specific in the ORDER BY clause like specifying more column in the ORDER BY clause
KH[spoiler]Time is always against us[/spoiler]
thank you,khtan; yes, if I am more specific in order by clause, the result is always arranged. But was strange to me to discover that order by can return "random" results.melic |
|
|
|
melic
Starting Member
4 Posts |
Posted - 2012-10-17 : 04:04:13
|
quote:
Originally posted by visakh16ordering by bit fields? not sure why you want to do thatyou might have to add some product related fields also------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
thank you for your reply visakh16; I solved adding 1 more field in order by.melic |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-17 : 23:09:55
|
| coolGlad that you sorted it out!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Order by is random?