group by does not sort

An user just posted an interresting question today. Why the Group By is crazy?

I summarize his example

SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
    NUM
——-
    400
 220000
    310

Well, group by is “sorting”, but how? this seems crazy. Oracle use the sort algorythm he wants. He can ascending-sort, descending-sort, hash-sort, or any other internal algorythm.

Here, 220000 is before 310 because it is smaller in bytes.

Have a look

SELECT num,dump(num) FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
       NUM DUMP(NUM)
———- ————————-
       400 Typ=2 Len=2: 194,5
    220000 Typ=2 Len=2: 195,23
       310 Typ=2 Len=3: 194,4,11

Well, if you need to sort, use order by and read tom blog

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>