Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 logic of group by in sql server

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-23 : 10:06:37
I have a table similar to this


organisatio n | id
XYZ | 1
ABC | 2
ATC | 3
GAC | 4
ATA | 5
CAT | 6
ARE | 7
ATE | 8


When I run this query,
 select organisation from table 

It produces the output as it is, the order also same as of table

However, when I run this query
 select organisation from table group by organisation

Though it produces all rows as in the table, it shuffles rows. The order is not same as of table.

I wonder what causes it to shuffle rows after I add group by clause. Is there any logic behind this in SQL Server?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 10:44:57
SQL Server does not guarantee the order of the output data unless you specify an explicit order by clause. When you don't specify an order by clause, the order that you see is whatever happened to be the ordering scheme that came out of the query execution. The fact that in the first case the data was ordered as you wanted it to was just a coincidence. It is not guaranteed to be repeatable.

So use:
select organisation from table order by organization;
select organisation from table group by organisation order by organization;
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-23 : 15:24:13
Thanks James for the explanation.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 16:39:18
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -