Sunday, August 4, 2013

Strange Behaviour of 'GROUP BY' clause when used without 'ORDER BY' clause

Using GROUP BY clause without using 'ORDER BY' clause

 A table named HOSTEL(hostel_no NUMBER, no_of_stud NUMBER)  is created and populated with values as follows : 
--------------------------------------
hostel_no|     no_of_stud     |
--------------------------------------
1001       |             124          |
1001       |             234          |
1002       |               97          |
1002       |             134          |
1003       |             234          |
1004       |             123          |
1004       |               89          |
1001       |               67          |
-------------------------------------

When GROUP BY command is applied as : 
SELECT hostel_no, sum(no_of_stud) as total_students FROM
HOSTELS GROUP BY hostel_no;

, and the output comes as:
--------------------------------------
hotel_no  |     total_students |
--------------------------------------
1003        |            234           |
1001        |            425           |
1002        |            231           |
1004        |            212           |
--------------------------------------

Now, the questions arise : 
  • Why the hostel_num 1003 came at the top ?
  •  Shouldn't it come at row number 3 ?
  •  What is the logic behind this kind of order ?

It is  known, that GROUP BY clause consolidates the repetitive column values and group them together. Now the doubt here is: How it decides the order of displaying those values, as here, one would expect an output like this :
--------------------------------------
hotel_no  |     total_students |
--------------------------------------
1001        |      425                 |
1002        |      231                 |
1003        |      234                 |
1004        |      212                 |
--------------------------------------

No comments:

Post a Comment