Re: Problem with the group statement...

"Larry Coon" <larry_at_assist.org> wrote in message news:3D335076.4A60_at_assist.org...
> Volker Hetzer wrote:
>
> > Hi!
> > I have trouble understanding the group statement. Here is
> > a small example:
> >
> > create temporary table x (a int, b int);
> > insert into x values(1,1);
> > insert into x values(1,2);
> > insert into x values(1,3);
> > insert into x values(2,1);
> > insert into x values(2,2);
> > insert into x values(2,3);
> > insert into x values(3,1);
> > insert into x values(3,2);
> > insert into x values(3,3);
> > select * from x group by a;
> >
> > Now, this gives me:
> > a b
> > 1 1
> > 2 1
> > 3 1
> >
> > However, I had expected it to give the whole table.
>
> Typically, when you use GROUP BY every item in the
> list of columns either appears in the GROUP BY clause
> or is an aggregate function. (I said "typically"
> because I thought that was a rule, but since it
> obviously works for you, I'm open to the possibility
> that this is something in the standard about which
> I am not aware.)
>
> At the very least, this is not a canonical use of the
> GROUP BY. What, semantically, are you trying to
> accomplish with your query?

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.

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?

Greetings and lots of thanks!
Volker Received on Tue Jul 16 2002 - 10:46:01 CEST