Re: Problem with the group statement...

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:agv7tc$md1$1_at_dackel.pdb.sbs.de...
> 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.
> The "Introduction to database systems" says:
> "... by (conceptually) rearranging it [the table] into the minimum
> number of groups such thaqt within any one group all rows have the same
> value for the combination of columns identified by the GROUP BY clause."
>
> Well, I specified only a in the group by clause and I think, there's
nothing
> to prevent theselect statement to put three rows in a group.
> Obviously, my thinking is faulty but I'd really like to know where.
>
> Greetings and thanks!
> Volker

I believe your select statement is not correct standard SQL. All columns in the select list must be either aggregate expressions or columns specified in the GROUP BY clause. The result you reported seems to be as if your SQL statement had been written thus:

select a, min(b) from x group by a; Received on Mon Jul 15 2002 - 23:22:51 CEST