Re: URGENT: sql group by clause has stopped working

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 9 Aug 2002 17:36:32 +0100
Message-ID: <3d53ef90$0$12039$ed9e5944@reading.news.pipex.net>

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D53E041.3E30DC26_at_exesolutions.com...

select owner
from smith
where job = 'comp'
and branch = '9'
group by owner;

<snip>

> That query never worked. You may have run it. It may have returned
something. But it never
> worked as it is written in your original posting.

It depends what you mean by 'worked'. If you mean that the query is meaningless then you are probably correct. The same results could be achieved by using the distinct keyword. However it is perfectly valid sql will run and will return the list of distinct owners (that satisfy the where clause). I think that what is happening is illustrated by the test below. The query appears to return duplicate rows because a character field has trailing spaces.

SQL> create table smith(owner,job,branch) 2 as select object_name,status,object_id from all_objects;

Table created.

SQL> insert into smith values ('blah','comp',9);

1 row created.

SQL> commit;

Commit complete.

SQL> select owner
  2 from smith
  3 where job = 'comp'
  4 and branch = '9'
  5 group by owner;

OWNER

blah

SQL> insert into smith values ('blah ','comp',9);

1 row created.

SQL> commit;

Commit complete.

SQL> select owner
  2 from smith
  3 where job = 'comp'
  4 and branch = '9'
  5 ;

OWNER

blah
blah

SQL> insert into smith values ('blah','comp',9);

1 row created.

SQL> commit;

Commit complete.

SQL> select owner
  2 from smith
  3 where job = 'comp'
  4 and branch = '9'
  5 group by owner;

OWNER

blah
blah

SQL> spool off

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Fri Aug 09 2002 - 11:36:32 CDT