October 30, 2019 at 2:15 am
In the attached two pictures, the only difference is the ordering of the columns in the GROUP BY clause. In one example, the grouping in the result set seems to be sorted by Gender. In the other example, there doesn't appear to be a sort in the result set among the columns in GROUP BY. Otherwise the results are the same in both cases.
How does the ordering of the columns in GROUP BY effect the way the data is presented in the result set?
October 30, 2019 at 3:33 am
The ONLY way top guarantee the order of the order of the ordering of the result set is to use an ORDER BY.
October 30, 2019 at 1:23 pm
The ONLY way top guarantee the order of the order of the ordering of the result set is to use an ORDER BY.
How about the disorderly orderedness of the order of the ordered ordering? 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 30, 2019 at 7:30 pm
Michael,
As DesNorton has already indicated, the ONLY way to guarantee a given order of the output rows, is to use ORDER BY. What has occurred in your first picture is that it just happened that your data was returned in what "appears to be" a "specific order". That was not what actually occurred, however misleading the result may be. GROUP BY affects the execution plan significantly; as well as the query results; and may often do so in a way that will produce a particular order, but there is no guarantee that such will occur, and should properly be considered a lucky coincidence whenever that order happens to agree with your desired result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 30, 2019 at 11:46 pm
The reason it is ordered is the database engine will need to do a sort to group all the columns in the group by that are equal together. From your results it looks like the engine starts the comparison with the left most column and works its way along. This is no guarantee that it will always return the results in the specified order, for example, a parallel execution plan might change that. The ONLY way, as everyone else has pointed out, to guarantee the sort order is to use the order by keyword to specify the sort order.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply