File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Having Clause can work without group by Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Having Clause can work without group by" Watch "Having Clause can work without group by" New topic
Author

Having Clause can work without group by

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 222

I did not know that having can be used without group by in the select list.

But Oracle docs made it clear that having can work without group by also.

The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group.


create table gotcha(id number);
insert into gotcha values(1);
insert into gotcha values(2);
insert into gotcha values(1);

select count(*)
from gotcha
having count(*) > 3;

no rows selected

select count(*)
from gotcha
having count(*) >2

count(*)
----------
3


Oracle Java Programmer , Oracle SQL Expert
Roger Sterling
Ranch Hand

Joined: Apr 06, 2012
Posts: 426

Yea, Oracle may work without a Group but Santa Clause needs a Group. Elf, there's just too much work to do by himself.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1606
    
  13

There is an implicit group in your results i.e. a single group including the entire set of data that you're fetching, because you haven't specified any other group. I can't think of many situations where using a HAVING clause without an explicit GROUP BY would be especially useful, though.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Having Clause can work without group by
 
Similar Threads
Group By Expr
Updateable ResultSet just for doing inserts: good idea?
delete duplicate rows
JOIN syntax for HQL . Need help!
How do i get result that i want?