Re: Problem with the group statement...

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:<ah0mga$jo9$1_at_dackel.pdb.sbs.de>...
> "Larry Coon" <larry_at_assist.org> wrote in message news:3D335076.4A60_at_assist.org...
> > Volker Hetzer wrote:
> >

[...]

> Well...
> I am having trouble figuring out what gets sorted and what gets
> grouped when I mix those two. So I decided to start simple
> and try out the group clause on its own with a very simple
> statement. Which produced the puzzling result above.
>

Grouping does not give you any particular order. The db will give you the rows in any order it find apropiate for the moment.

Normally you use the group by clause together with an aggregate function like sum, avg, etc. Ex:

select sum(b) as c from x

C

         18

If you want to sum each individual "A" value you group the output by a as in:

select a, sum(b) as c from x group by a

A C
----------- -----------

          1           6
          2           6
          3           6

Note that the rows in this example might be returned in 3*2*1=6 different orders

(1,2,3), (1,3,2), (2,1,3), (2,3,1), (3,2,1), (3,1,2)

To get the rows in a particular order you add the "order by" clause as in:

select a, sum(b) as c from x group by a order by a

A C
----------- -----------

          1           6
          2           6
          3           6

you can also choose to order them descending as in

select a, sum(b) as c from x group by a order by a desc

A C
----------- -----------

          3           6
          2           6
          1           6
 

> Btw, what *does* happen when I use group and order?
> Will the ordered rows be grouped (therefore messing
> up the order), will the rows within a group (if it's possible
> at all to have more than one row in a group) be sorted
> or, if group creates exactly one row per group, will
> the groups be sorted?
>

the order by is applied last

HIH
/Lennart

[...] Received on Wed Jul 17 2002 - 19:06:12 CEST