Re: need confirmation on a SELECT statement

On Fri, 8 Sep 2000, Arne Borkowski (borko.net) wrote: <snip> > The statement goes here: > > $sql = "SELECT p.id, p.name, p.price, b.sid, b.artno, count(b.artno) " > $sql .= "FROM products p, basket b " > $sql .= "WHERE (p.id = b.artno) AND (b.sid = '$sid') " > $sql .= "GROUP BY p.id, b.artno " > > > If I want to run it with InterBase, I need to add ALL columns to the GROUP > BY > clause. Although I am not SQL guru, that seems strange to me. > > This form works and need somebody to tell me if this is my lach of SQL or a > specialty > in PHP / InterBase interconnection ... (I couldn't believe that either, to > be honest) > > > $sql = "SELECT p.id, p.name, p.price, b.sid, b.artno, count(b.artno) " > $sql .= "FROM products p, basket b " > $sql .= "WHERE (p.id = b.artno) AND (b.sid = '$sid') " > $sql .= "GROUP BY p.id, p.name, p.price, b.sid, b.artno " Well, the same thing happens with PostgreSQL, so it is not an Interbase only thing. In the PostgreSQL User's Manual, it states: "When GROUP BY is present, it is not valid for the SELECT output expression(s) to refer to ungrouped columns except within aggregrate functions, since there would be more than one possible value to return for an ungrouped column." So it *appears* that Interbase (and PostgreSQL) are doing "the right thing." John