Question about Group By

Hi All,
I am having a problem with a group of results being returned by a
query with a group by. Basically the data is being populated into a
table (BulkLoad) from a legacy system in a predetermined order. When I
run a query on that data (without any order bys) the data is comming
out in different order then in the order in the table. Does Group by
implicitly order results?
This is an issue because order by wouldn't put the rows in the
correct order, it's ordered by other attributes. Is there any way to
stop it from doing that?
the unordered data looks like (correct)
Q0001Y
Q0001N
Q0002Y
Q0003N
and the group by makes it into (incorrect)
Q0001N
Q0001Y
Q0002Y
Q0003NOrder of rows in a table is never guaranteed (even with a clustered
index ;-) , if you need to order output use ORDER BY
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Red2 wrote:
> Hi All,
> I am having a problem with a group of results being returned by a
> query with a group by. Basically the data is being populated into a
> table (BulkLoad) from a legacy system in a predetermined order. When I
> run a query on that data (without any order bys) the data is comming
> out in different order then in the order in the table. Does Group by
> implicitly order results?
> This is an issue because order by wouldn't put the rows in the
> correct order, it's ordered by other attributes. Is there any way to
> stop it from doing that?
> the unordered data looks like (correct)
> Q0001Y
> Q0001N
> Q0002Y
> Q0003N
> and the group by makes it into (incorrect)
> Q0001N
> Q0001Y
> Q0002Y
> Q0003N|||This is probably one of the most important things to understand about
SQL--the logical aspects of the language vs. the storage of the data and the
physical processing of the query by the database platform you are working
with.
SQL deals with sets, and one of the most fundamental aspects of a set is
that it is UNORDERED.
As far as the language is concerned, a query with no ORDER BY clause does
not guarantee any particular order of the result set (with or without a
GROUP BY).
Therefore, if you need the result to be returned in a particular order, the
only way to guarantee this is by explicitly specifying an ORDER BY clause;
period.
In terms of data storage, even when you create an index on the table, it
"logically sorts the data"; there's no guarantee that the data will be
physically organized in index order in the data files.
In terms of physical processing, even if the version of SQL Server you are
working with supports only certain access methods that process a given
language element (e.g., GROUP BY) in a certain order, you should never rely
on this behavior because future versions or even service packs can change
the behavior as long as the language requirements in terms of the logical
correctness result set are met.
For example, prior to SQL Server 7.0 the only algorithm available to the
optimizer to perform GROUP BY was a sort-based algorithm, which always
resulted in the output being sorted by the GROUP BY list. SQL Server 7.0
introduced hash-based grouping, which does not guarantee that the output
will be sorted by the GROUP BY list.
In short, to save you grief, specify ORDER BY if you need the data sorted.
Otherwise, don't have any expectations in terms of the order of the output.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Red2" <sdibello@.gmail.com> wrote in message
news:1149682973.983254.90760@.i40g2000cwc.googlegroups.com...
> Hi All,
> I am having a problem with a group of results being returned by a
> query with a group by. Basically the data is being populated into a
> table (BulkLoad) from a legacy system in a predetermined order. When I
> run a query on that data (without any order bys) the data is comming
> out in different order then in the order in the table. Does Group by
> implicitly order results?
> This is an issue because order by wouldn't put the rows in the
> correct order, it's ordered by other attributes. Is there any way to
> stop it from doing that?
> the unordered data looks like (correct)
> Q0001Y
> Q0001N
> Q0002Y
> Q0003N
> and the group by makes it into (incorrect)
> Q0001N
> Q0001Y
> Q0002Y
> Q0003N
>|||Red2 wrote:
> Hi All,
> I am having a problem with a group of results being returned by a
> query with a group by. Basically the data is being populated into a
> table (BulkLoad) from a legacy system in a predetermined order. When I
> run a query on that data (without any order bys) the data is comming
> out in different order then in the order in the table. Does Group by
> implicitly order results?
> This is an issue because order by wouldn't put the rows in the
> correct order, it's ordered by other attributes. Is there any way to
> stop it from doing that?
> the unordered data looks like (correct)
> Q0001Y
> Q0001N
> Q0002Y
> Q0003N
> and the group by makes it into (incorrect)
> Q0001N
> Q0001Y
> Q0002Y
> Q0003N
Tables aren't ordered. The only way to order query results is to use
ORDER BY. If you don't populate the table with the necessary data on
which to sort then you don't have enough information to solve your
problem.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for all the help.
So from one table to another table, with identical data, group by my do
different things
got it...
:)